常用select语句

1、从pb导出数据：
select * from ts_mxh_user_info where stat_month=200712 and city_code='E19A'; 另存类型Dbase3
select * from ts_mxh_user_info where stat_month=200901 and city_code='E19A' and serial_number like ‘13%’;
select * from ts_mxh_user_info where stat_month=200901 and city_code='E19A' and serial_number not like ‘13%’;
select * from ts_mxh_user_info where stat_month=200901 and city_code='E19B'
select * from ts_mxh_user_info where stat_month=200901 and city_code='E19C'
select * from ts_mxh_user_info where stat_month=200901 and city_code='E19D'
select * from ts_mxh_user_bill where stat_month=200901
2、追加记录：
use e:\mbg\info0712
appen from e:\mbg\info0712_1
appen from e:\mbg\info0712_2
3、核对info表：
PB:select count(*),sum(bill_fee/100) from ts_mxh_user_info where stat_month=200712 ;
VF:sele sum(bill_fee/100) from e:\mbg\info0812
VF:sele sum(fee_sum) from e:\mbg\bill0812
VF:sele sum(debt_fee/100) from e:\mbg\info0812
VF:sele sum(fee_sum) from e:\mbg\debt0812
4、提取公话数据：
引用e:\mbg\移动公话用户每月欠费清单
提取乡镇团场公话通话统计
Do e:\mbg\计算每月通话用户数_07年乡镇团场公话通话统计.PRG
modify command e:\mbg\统计移动公话每月欠费清单.PRG
do e:\mbg\统计移动公话每月欠费清单.PRG
modify command e:\mbg\2008年公话相关数据统计_原件.prg
5、各营业部净增通话：发电子邮件
计算每月通话用户数_07年乡镇团场营业厅净增通话统计0802.PRG
计算每月净增通话统计表的指标语句.PRG
计算营业厅当月欠费回收率_2007年5月重新改0802.prg
统计移动公话和红名单等当月欠费回收率.prg
6、关联集团信息化收入、短信费
sele * from e:\mbg\集团信息化收入统计表_总个数577.dbf group by 手机号码
sele a.*,b.city_code,b.bill_fee/100 as 收入12月,bill_fee_m/100 as 短信费12月from e:\mbg\集团信息化收入统计表_总个数577.dbf a,e:\mbg\info0712 b where a.手机号码=b.serial_num and b.bill_fee/100>0
copy to e:\mbg\t1
sele * from e:\mbg\集团信息化收入统计表_总个数577 where 手机号码not in (sele 手机号码from e:\mbg\t1)
copy to e:\mbg\t2
close all
use e:\mbg\t1
appen from e:\mbg\t2
copy to e:\mbg\集团信息化收入统计表_总个数580_12月收入type xls
7、sele 手机号,count(*) from e:\mbg\2007年9月末梢代办放号清单group by 手机号
8、提取零次用户清单发电子邮件给市场部主任业务区经理副经理管理员
Do e:\mbg\提取每月零次用户的情况.PRG
9、提取乡镇团场公话通话统计
Do e:\mbg\计算每月通话用户数_07年乡镇团场公话通话统计.PRG
10、计算代销代办每月入网第4个月的在网率
核实号码是否重复：sele 手机号,count(*) from e:\mbg\4月末稍代办放号清单 group by 手机号
copy to e:\mbg\t3
sele * from e:\mbg\t3 where cnt>1
sele er_id,a.* from e:\mbg\2007年9月末梢代办放号清单 a,e:\mbg\info0709 b where a.手机号=b.serial_num and b.arrive_tag='1'
copy to e:\mbg\t0
sele a.*,er_state,b.debt_fee/100 as 往月欠费,stat_month as 统计月份from e:\mbg\t0 a,e:\mbg\info0712 b where er_id=er_id order by 业务区,部门,代销商
copy to e:\mbg\t1
select a.*,erstate as 状态from e:\mbg\t1 a,e:\mbg\userstate b where er_state=rstateco
copy to e:\mbg\2007年9月末梢代办放号清单_在第4个月清单
copy to e:\mbg\2007年9月末梢代办放号清单_在第4个月清单 type xls
sele 业务区,部门,代销商,count(*) as 在网数 from e:\mbg\2007年9月末梢代办放号清单_在第4个月清单 where 状态 in ('开通','高额停机') and 往月欠费=0 group by 业务区,部门,代销商 order by 业务区,部门
copy to e:\mbg\2007年9月末梢代办放号清单_在第4个月在网汇总 type xls
sele er_id,a.* from e:\mbg\2007年9月营销员放号清单.dbf a,e:\mbg\info0709 b where a.手机号=b.serial_num and b.arrive_tag='1'
copy to e:\mbg\t0
sele a.*,er_state,b.debt_fee as 往月欠费,stat_month as 统计月份from e:\mbg\t0 a,e:\mbg\info0712 b where er_id=er_id order by 业务区,营销员姓名
copy to e:\mbg\t1
select a.*,erstate as 状态from e:\mbg\t1 a,e:\mbg\userstate b where er_state=rstateco
copy to e:\mbg\2007年9月营销员放号清单_在第4个月清单
copy to e:\mbg\2007年9月营销员放号清单_在第4个月清单 type xls
sele 业务区,营销员姓名,count(*) as 在网数 from e:\mbg\2007年9月营销员放号清单_在第4个月清单 where 状态 in ('开通','高额停机') and 往月欠费=0 group by 业务区,营销员姓名 order by 业务区,营销员姓名
copy to e:\mbg\2007年9月营销员放号清单_在第4个月在网汇总 type xls
10、sele from where a.in_date>={^2007-7-1} and a.substr(SERVICE_US,2,1)<>'1 and
a.public_tag='0' and
b.
11、sele 负责人所在,负责人,count(*),sum(欠费金额),sum(扣罚金额) from e:\mbg\扣罚明细清单080112 group by 负责人所在,负责人
12、李斌(李斌) 11:19:25
就是在7月以后入网的用户从来没有开通过彩铃业务，剔除公话及买希腊普卡用户，要用户的手机号码及消费及是否开通
sele serial_num,user_id,bill_fee/100 as 消费,user_state as 状态from e:\mbg\info0712 where arrive_tag='1' and in_date>={^2007-7-1} and substr(SERVICE_US,2,1)<>'1' and public_tag not in ('1','2','3') and user_id in (sele user_id from e:\mbg\discnt0712 where discnt_cod<>20000416 and discnt_cod<>20001933)
copy to e:\mbg\t1
sele a.*,erstate from e:\mbg\t1 a,e:\mbg\userstate b where a.状态=rstateco
copy to e:\mbg\未开通彩铃清单_李斌数据type xls
copy to e:\mbg\未开通彩铃清单_李斌数据deli with tab
13 、前两个月月消费均在100-300元；2、客户品牌神州行天山通；2、需求1600户。

sele stat_month,user_id,serial_num as 手机号码,cust_name as 客户姓名,city_code as 业务区,in_date as 开户时间,folk_code as 民族,age as 年龄,bill_fee/100 as 本月消费from e:\mbg\info0712 where brand_code='G002' and user_state='0' and arrive_tag='1' and bill_fee/100>=100 and bill_fee/100<=300
copy to e:\mbg\t1
sele a.*, b.bill_fee/100 as 上月消费from e:\mbg\t1 a,e:\mbg\info0711 b where er_id=er_id and b.bill_fee/100>=100 and b.bill_fee/100<=300
copy to e:\mbg\t2
sele a.*,b.folk_name as 民族from e:\mbg\t2 a,e:\mbg\folk b where a.民族=b.folk_code copy to e:\mbg\t3
sele * from e:\mbg\t2 where user_id not in (sele user_id from e:\mbg\t3 )
copy to e:\mbg\t4
close all
use e:\mbg\t3
appen from e:\mbg\t4
sele * from e:\mbg\t3
copy to e:\mbg\外呼数据_天山通type xls
14、有GPRS上网记录；2、无GPRS 5元以上套餐;3、数据需求1000户。

(近两个月的GPRS流量在1元以上，月消费在50元以上的未办理GPRS套餐的客户群。

2000户)
close all
sele stat_month,user_id,serial_num as 手机号码,cust_name as 客户姓名,city_code as 业务区,in_date as 开户时间,age as 年龄,bill_fee/100 as 消费,BILL_FEE_G/100 as GPRS费用from e:\mbg\info0803 wher user_state='0' and arrive_tag='1' and BILL_FEE_G/100>1 and bill_fee/100>50 and user_id not in (sele user_id from e:\mbg\discnt0803 where discnt_cod=10 or discnt_cod=11 or discnt_cod=2 or discnt_cod=9 or discnt_cod=90990049 or discnt_cod=90990061 or discnt_cod=90990512 or discnt_cod=90990514 or discnt_cod=90990516 or discnt_cod=90990513 or discnt_cod=90990515)
copy to e:\mbg\t1
sele a.*,b.BILL_FEE_G/100 as 十一月GPRS费用from e:\mbg\t1 a,e:\mbg\info0711 b wher er_id=er_id
copy to e:\mbg\t2
sele * from e:\mbg\t1 where user_id not in (sele user_id from e:\mbg\t2)
copy to e:\mbg\t3
close all
use e:\mbg\t2
appen from e:\mbg\t3
copy to e:\mbg\t4
sele a.*,b.BILL_FEE_G/100 as 十月GPRS费用from e:\mbg\t4 a,e:\mbg\info0710 b wher er_id=er_id
copy to e:\mbg\t5
sele * from e:\mbg\t4 where user_id not in (sele user_id from e:\mbg\t5)
copy to e:\mbg\t6
close all
use e:\mbg\t5
appen from e:\mbg\t6
close all
sele a.*,b.folk_name as 民族from e:\mbg\t5 a,e:\mbg\folk b where a.民族=b.folk_code
sele * from e:\mbg\t5 where user_id not in (sele user_id from e:\mbg\t7)
copy to e:\mbg\t8
close all
use e:\mbg\t7
appen from e:\mbg\t8
sele * from e:\mbg\t7
copy to e:\mbg\外呼数据2_无GPRS套餐type xls
各业务区欠费清单(含专营店合作厅和乡镇团场).rar
发邮件给
2007年9月营销员放号清单_在第4个月在网汇总.XLS2007年9月末梢代办放号清单_在第4个月在网汇总.XLS
发邮件给
2007年12月专营店和合作厅的当月欠费回收率.XLS
发邮件给
欠费在300元以上用户清单：
sele user_id,city_code as 业务区,develop_de,serial_num as 手机号码,cust_name as 客户姓名,in_date as 开户时间,destroy_ti as 注销时间,user_state,min_debt_b as 最早欠费月份,prepay_tag as 预付费标志,red_tag as 红名单标志,credit_val/100 as 信用度from e:\mbg\info0802 where debt_fee/100>=300 and serial_num like '13%'
copy to e:\mbg\t1
sele a.*,erstate as 状态from e:\mbg\t1 a,e:\mbg\userstate b where er_state=rstateco
sele a.*,b.部门名称from e:\mbg\t2 a,e:\mbg\depart_id b where a.develop_de=b.部门标识copy to e:\mbg\欠费在300元以上用户清单
sele b.stat_month as 统计月份,a.*,b.debt_fee/100 as 欠费from e:\mbg\欠费在300元以上用户清单a,e:\mbg\info0802 b where er_id=er_id
copy to e:\mbg\欠费在300元以上用户清单type xls
是否加六元数据营销包？
sele * from e:\mbg\2月清单汇总group by 手机号码
sele a.*,er_id from e:\mbg\2月清单汇总a,e:\mbg\info0802 b where a.手机号码=b.serial_num and arrive_tag='1'
copy to e:\mbg\t1
sele a.*,b.discnt_cod as 数据包from e:\mbg\t1 a,e:\mbg\discnt0802 b where er_id=er_id and (b.discnt_cod =90990093 or b.discnt_cod =90990094)
copy to e:\mbg\t2
sele * from e:\mbg\t1 where 手机号码not in (sele 手机号码from e:\mbg\t2)
copy to e:\mbg\t3
close all
use e:\mbg\t2
appen from e:\mbg\t3
copy to e:\mbg\2月清单汇总_数据包type xls
根据公话交接清单计算当月欠费回收率
Sele * from e:\mbg\阿拉山口公话交接清单group by 手机号码
close all
use e:\mbg\阿拉山口公话交接清单
modify struc
repl all 手机号码with alltrim (手机号码)
close all
sele a.*,b.* from e:\mbg\阿拉山口公话交接清单.dbf a,e:\mbg\info0801 b where a.手机号码=b.serial_num and arrive_tag='1'
copy to e:\mbg\t1
sele a.手机号码,a.cust_name as 客户姓名,b.FEE_SUM as 当月总欠费from e:\mbg\t1 a,e:\mbg\debt0802 b where er_id=er_id and b.DEBT_BCYC_=200801
copy to e:\mbg\阿拉山口公话_当月欠费清单type xls
sele * from e:\mbg\阿拉山口公话交接清单where 手机号码not in (sele serial_num from e:\mbg\t1)
copy to e:\mbg\t2
close all
use e:\mbg\t1
appen from e:\mbg\t2
sele
a.*,
b.DEBT_BCYC_,b.FEE_SUM,b.FEE_BASE,b.FEE_CALL,b.FEE_OPFC,b.FEE_DATA,b.FE E_INFO,b.FEE_CMNT,b.FEE_OTHE,b.FEE_SMS from e:\mbg\t1 a,e:\mbg\debt0802 b where er_id=er_id and b.DEBT_BCYC_=200801
copy to e:\mbg\阿拉山口公话欠费清单
sele * from e:\mbg\t1 where user_id not in (sele user_id from e:\mbg\阿拉山口公话欠费清单) copy to e:\mbg\t3
close all
use e:\mbg\阿拉山口公话欠费清单
appen from e:\mbg\t3
sele develop_de,sum(bill_fee/100) as 当月应收,sum(fee_sum) as 当月欠费from e:\mbg\阿拉山口公话欠费清单where public_tag in ('1','2','3') group by develop_de
copy to e:\mbg\阿拉山口公话当月欠费
select a.县市名称,a.部门名称,a.部门标识,b.当月应收,b.当月欠费from e:\mbg\depart_id a,e:\mbg\阿拉山口公话当月欠费 b where a.部门标识=b.develop_de
copy to e:\mbg\阿拉山口公话当月欠费_部门
sele * from e:\mbg\阿拉山口公话当月欠费_部门where 部门标识not in (sele develop_de from e:\mbg\计算城区公话的参数表.dbf where 是否农村='农村')
copy to e:\mbg\t0
sele 县市名称,sum(当月应收) as 当月应收,sum(当月欠费) as 当月欠费from e:\mbg\t0 group by 县市名称
copy to e:\mbg\s0
sele 县市名称,sum(当月应收) as 当月应收,sum(当月欠费) as 当月欠费,(当月应收-当月欠费)/当月应收as 欠费回收率from e:\mbg\s0 group by 县市名称
copy to e:\mbg\阿拉山口公话交接清单_当月欠费回收率type xls
精河1月有价值客户在2月的流失情况：（1月消费在120元以上的客户在2月消费小于120元的客户清单）
sele user_id,develop_de,serial_num from e:\mbg\info0801 where city_code='E19B' and bill_fee/100>=120
copy to e:\mbg\1月消费在120元以上用户
sele a.*,b.city_code as 业务区,b.serial_num as 手机号码,b.cust_name as 客户姓名,b.bill_fee/100 as 消费,b.home_addre as 家庭地址,b.contact_ph as 联系电话from e:\mbg\1月消费在120元以上用户a,e:\mbg\info0802 b where er_id=er_id and b.bill_fee/100<120
copy to e:\mbg\t2
sele a.*,b.部门名称from e:\mbg\t2 a,e:\mbg\depart_id b where a.develop_de=b.部门标识
copy to e:\mbg\精河有价值客户在2月流失清单type xls
大河沿和哈镇手机号码
close all
sele stat_month,user_id,serial_num as 手机号码,user_state as 状态from e:\mbg\info0802 where arrive_tag='1' and brand_code like 'G0%' and (develop_de='56954' or develop_de='11963' or develop_de='11967' or develop_de='43389' or develop_de='43388')
copy to e:\mbg\t1
sele a.*,erstate as 状态from e:\mbg\t1 a,e:\mbg\userstate b where a.状态=rstateco copy to e:\mbg\大河沿和哈镇手机号码type xls
1、未开通彩铃的客户；
2、消费在50元以上；
3、入网时间在2007年12月1日前
sele user_id,serial_num as 手机号码,cust_name as 客户姓名,city_code as 业务区,in_date as 入网时间,bill_fee/100 as 消费,age as 年龄from e:\mbg\info0802 where brand_code like 'G0%' and user_state='0' and in_date<{^2007-12-1} and substr(SERVICE_US,2,1)<>'1' and public_tag not in ('1','2','3') and user_id in (sele user_id from e:\mbg\discnt0802 where discnt_cod<>20000416 and discnt_cod<>20001933)
copy to e:\mbg\t1
sele * from e:\mbg\t1 where 消费>50
copy to e:\mbg\未开通来电管家数据_外呼需求type xls
1、客户年龄在15-25岁之间；
2、客户品牌神州行天山通；
3、客户月消费在50-100元之间；
4、点对点短信费用在10元以上
sele user_id,serial_num as 手机号码,cust_name as 客户姓名,city_code as 业务区,in_date as 入网时间,bill_fee/100 as 消费,age as 年龄from e:\mbg\info0802 where brand_code='G002' and user_state='0' and bill_fee_m/100>10
copy to e:\mbg\t1
sele * from e:\mbg\t1 where 15<年龄and 年龄<25 and 50.0000<消费and 消费<100.0000
copy to e:\mbg\动感地带目标客户_外呼需求type xls
1、前两个月月主叫通话分钟数在650分钟以上；
2、客户品牌神州行天山通；剔除公话
2、前两个月消费在120元以上
sele user_id,serial_num as 手机号码,cust_name as 客户姓名,city_code as 业务区,in_date as 入网时间,bill_fee/100 as 消费,age as 年龄from e:\mbg\info0803 where brand_code='G002' and public_tag not in (‘1’,’2’,’3’) and bill_fee/100>=120
copy to e:\mbg\tt1
sele a.* from e:\mbg\tt1 a, e:\mbg\info0802 b where er_id=er_id and b. bill_fee/100>=120
copy to e:\mbg\t1
use e:\mbg\call0803
modify struc
sele a.* from e:\mbg\t1 a,e:\mbg\call0803 b where er_id=er_id and b.call_timez>=650 copy to e:\mbg\t2
use e:\mbg\call0802
modify struc
sele a.* from e:\mbg\t2 a,e:\mbg\call0802 b where er_id=er_id and b.call_timez>=650 copy to e:\mbg\前两个月月主叫通话分钟数在650分钟以上的神州行客户清单
copy to e:\mbg\前两个月月主叫通话分钟数在650分钟以上的神州行客户清单 1 for recno()>825
copy to e:\mbg\前两个月月主叫通话分钟数在650分钟以上的神州行客户清单 2 for recno()<=825
copy to e:\mbg\前两个月月主叫通话分钟数在650分钟以上的神州行客户清单1 type xls for recno()>825
copy to e:\mbg\前两个月月主叫通话分钟数在650分钟以上的神州行客户清单2 type xls for recno()<=825
sele * from e:\mbg\前两个月月主叫通话分钟数在650分钟以上的神州行客户清单1 where 手机号码in (sele 手机号码from e:\mbg\前两个月月主叫通话分钟数在650分钟以上的神州行客户清单2)
提取每月销号客户清单：
modify command e:\mbg\每月销号客户清单
sele user_id,stat_month,city_code as 业务区,brand_code as 品牌,serial_num as 手机号码,in_date as 入网时间,cust_name as 客户姓名,user_state,home_addre as 家庭地址,contact_ph as 联系电话from e:\mbg\info0802 where destroy_ti>{^2008-1-31} and (user_state='6'or user_state='8' or user_state='9' or user_state='E')
copy to e:\mbg\t1
sele a.*,erstate as 状态from e:\mbg\t1 a,e:\mbg\userstate b where er_state=rstateco copy to e:\mbg\2008年2月销号客户清单deli with tab
copy to e:\mbg\2008年2月销号客户清单type xls
*!* 统计2007年12月份通话客户在2008年2月份未通话的客户清单
modify command e:\mbg\统计2007年12月份通话客户在2008年2月份未通话的客户清单
select serial_num as 手机号码,cust_name as 客户姓名,city_code as 业务区,develop_de,user_state,BRAND_CODE as 品牌,in_date as 开户时间,last_stop_ as 最后停机时间,home_addre as 家庭住址,contact_ph as 联系电话from f:\mbg\info0701 where call_time=0 and remove_tag in ('0','1','3') and user_id in (select user_id from f:\mbg\info0712 where call_time>0 ) into cursor s1
copy to f:\mbg\t1
select a.*,b.部门名称from f:\mbg\t1 a,f:\mbg\depart_id b where a.develop_de=b.部门标识
into cursor s1
copy to f:\mbg\t2
select a.*,erstate as 用户状态from f:\mbg\t2 a,f:\mbg\userstate b where er_state=rstateco into cursor s1
copy to f:\mbg\2007年12月份通话客户在2008年2月份未通话的客户清单type xls
全球通钻金银卡欠费停机申请预销停机客户清单
Do e:\mbg\全球通钻金银卡欠费停机申请预销停机客户清单.PRG
sele user_id,stat_month,serial_num as 手机号码,vip_tag ,user_state from e:\mbg\info0803 wher (vip_tag='2' or vip_tag= '3' or vip_tag='4') and (user_state='8' or user_state='F')
copy to e:\mbg\t1
sele a.*,erstate as 状态from e:\mbg\t1 a,e:\mbg\userstate b where er_state=rstateco copy to e:\mbg\t2
sele a.*,b.vip_name as 卡类级别from e:\mbg\t2 a,e:\mbg\td_m_vipclass b where a.vip_tag=b.vip_tag
copy to e:\mbg\2008年2月全球通钻金银卡欠费停机申请预销停机客户清单type xls
大客户服务中心集团彩铃需求数据清单：
sele * from e:\mbg\info0802 where arrive_tag='1' and in_date<={^2007-2-28} and substr(SERVICE_US,2,1)<>'0' and brand_code like 'G0%'
copy to e:\mbg\t1
sele * from e:\mbg\t1 where product_id<>10307001 and product_id<>10307002 and product_id<>10307003 and product_id<>36240002 and product_id<>36240003 and product_id<>36240004 and product_id<>90990188 and product_id<>90990237 and product_id<>90990240 and product_id<>90990377 and product_id<>90990378 and product_id<>90990379 and product_id<>90990380 and product_id<>283 and product_id<>296 and product_id<>297
copy to e:\mbg\t2
sele * from e:\mbg\t2 where user_id not in (sele user_id from e:\mbg\discnt0802 where discnt_cod=90990439 or discnt_cod=90990441 or discnt_cod=90990092 or discnt_cod=90990093 or discnt_cod=90990094 or discnt_cod=90990381 or discnt_cod=90990382 or discnt_cod=90990383 or discnt_cod=90990384 or discnt_cod=90990075 or discnt_cod=90990072 or discnt_cod=90990076 or discnt_cod=90990073 or discnt_cod=90990074 or discnt_cod=906 or discnt_cod=60000299 or discnt_cod=60000303 or discnt_cod=269)
copy to e:\mbg\t3
sele user_id,stat_month,develop_de,user_state,city_code as 业务区,serial_num as 客户号码,cust_name as 客户姓名,in_date as 入网时间,bill_fee/100 as 消费from e:\mbg\t3
copy to e:\mbg\t4
sele a.*,erstate as 状态from e:\mbg\t4 a,e:\mbg\userstate b where er_state=rstateco copy to e:\mbg\t5
select a.*,b.部门名称from e:\mbg\t5 a,e:\mbg\depart_id b where a.develop_de=b.部门标识copy to e:\mbg\大客户服务中心集团彩铃需求清单deli with tab
copy to e:\mbg\大客户服务中心集团彩铃需求清单1 type xls
全球通套餐优惠编码907 （200元包月）全球通200元包月包所有费用除国际费用.
906 （250元包月）
sele a.*,b.end_date from e:\mbg\info0811 a,e:\mbg\discnt0811 b where a.arrive_tag='1' and a.brand_code like 'G0%' and er_id=er_id and b.discnt_cod=907
copy to e:\mbg\t1
sele user_id,stat_month,develop_de,user_state,city_code as 业务区,serial_num as 手机号码,cust_name as 客户姓名,in_date as 入网时间,home_addre as 家庭地址,contact_ph as 联系电话,end_date from e:\mbg\t1
copy to e:\mbg\t2
sele a.*,erstate as 状态from e:\mbg\t2 a,e:\mbg\userstate b where er_state=rstateco
copy to e:\mbg\t3
select a.*,b.部门名称from e:\mbg\t3 a,e:\mbg\depart_id b where a.develop_de=b.部门标识copy to e:\mbg\截止11月200元包月清单type xls
sele * from e:\mbg\info0802 where arrive_tag='1' and brand_code like 'G0%'
copy to e:\mbg\t1
sele * from e:\mbg\t1 where user_id in (sele user_id from e:\mbg\discnt0802 where discnt_cod=906)
copy to e:\mbg\t2
sele user_id,stat_month,develop_de,user_state,city_code as 业务区,serial_num as 手机号码,cust_name as 客户姓名,in_date as 入网时间,home_addre as 家庭地址,contact_ph as 联系电话from e:\mbg\t2
copy to e:\mbg\t3
sele user_id,stat_month,develop_de,user_state,city_code as 业务区,serial_num as 手机号码,cust_name as 客户姓名,bill_fee/100 as 消费,in_date as 入网时间,home_addre as 家庭地址,contact_ph as 联系电话from e:\mbg\t2
copy to e:\mbg\t4
sele a.*,erstate as 状态from e:\mbg\t4 a,e:\mbg\userstate b where er_state=rstateco copy to e:\mbg\t5
select a.*,b.部门名称from e:\mbg\t5 a,e:\mbg\depart_id b where a.develop_de=b.部门标识copy to e:\mbg\t6
sele a.*,b.bill_fee/100 as 账单1月from e:\mbg\t6 a,e:\mbg\info0801 b where
er_id=
er_id
copy to e:\mbg\t7
sele a.*,b.bill_fee/100 as 账单0712月from e:\mbg\t6 a,e:\mbg\info0712 b where er_id=er_id
copy to e:\mbg\t8
sele * from e:\mbg\t7 where 手机号码not in (sele 手机号码from e:\mbg\t8 )
close all
use e:\mbg\t8
close all
sele * from e:\mbg\t7 where 手机号码not in (sele 手机号码from e:\mbg\t8 )
copy to e:\mbg\t9
close all
use e:\mbg\t8
append from e:\mbg\t9
sele a.*,b.bill_fee/100 as 账单0711月from e:\mbg\t8 a,e:\mbg\info0711 b where er_id=er_id
close all
sele a.*,b.bill_fee/100 as 账单0712月from e:\mbg\t7 a,e:\mbg\info0712 b where er_id=er_id
copy to e:\mbg\t8
sele * from e:\mbg\t7 where 手机号码not in (sele 手机号码from e:\mbg\t8 )
copy to e:\mbg\t9
close all
use e:\mbg\t8
append from e:\mbg\t9
sele a.*,b.bill_fee/100 as 账单0711月from e:\mbg\t8 a,e:\mbg\info0711 b where er_id=er_id
append from e:\mbg\t10
copy to e:\mbg\t10
sele * from e:\mbg\t8 where 手机号码not in (sele 手机号码from e:\mbg\t10 )
copy to e:\mbg\t11
close all
use e:\mbg\t10
append from e:\mbg\t11
close all
sele a.*,b.bill_fee/100 as 账单0710月from e:\mbg\t10 a,e:\mbg\info0710 b where er_id=er_id
copy to e:\mbg\t12
sele * from e:\mbg\t12 where 手机号码not in (sele 手机号码from e:\mbg\t10 )
sele * from e:\mbg\t10 where 手机号码not in (sele 手机号码from e:\mbg\t12 )
copy to e:\mbg\t13
close all
use e:\mbg\t12
append from e:\mbg\t13
sele a.*,b.bill_fee/100 as 账单0709月from e:\mbg\t12 a,e:\mbg\info0709 b where er_id=er_id
copy to e:\mbg\t14
sele * from e:\mbg\t12 where 手机号码not in (sele 手机号码from e:\mbg\t14 )
copy to e:\mbg\t15
close all
use e:\mbg\t14
append from e:\mbg\t15
sele a.*,b.bill_fee/100 as 账单0708月from e:\mbg\t14 a,e:\mbg\info0708 b where er_id=er_id
copy to e:\mbg\t16
sele * from e:\mbg\t14 where 手机号码not in (sele 手机号码from e:\mbg\t16 )
copy to e:\mbg\t17
close all
use e:\mbg\t16
append from e:\mbg\t17
sele a.*,b.bill_fee/100 as 账单0707月from e:\mbg\t16 a,e:\mbg\info0707 b where er_id=er_id
copy to e:\mbg\t18
sele * from e:\mbg\t16 where 手机号码not in (sele 手机号码from e:\mbg\t18 )
copy to e:\mbg\t19
close all
use e:\mbg\t18
append from e:\mbg\t19
sele a.*,b.bill_fee/100 as 账单0706月from e:\mbg\t18 a,e:\mbg\info0706 b where er_id=er_id
append from e:\mbg\t20
copy to e:\mbg\t20
sele * from e:\mbg\t18 where 手机号码not in (sele 手机号码from e:\mbg\t20 )
copy to e:\mbg\t21
close all
use e:\mbg\t20
append from e:\mbg\t21
sele a.*,b.bill_fee/100 as 账单0705月from e:\mbg\t20 a,e:\mbg\info0705 b where er_id=er_id
copy to e:\mbg\t22
sele * from e:\mbg\t20 where 手机号码not in (sele 手机号码from e:\mbg\t22 )
copy to e:\mbg\t23
close all
use e:\mbg\t22
append from e:\mbg\t23
sele a.*,b.bill_fee/100 as 账单0704月from e:\mbg\t22 a,e:\mbg\info0704 b where er_id=er_id
copy to e:\mbg\t24
sele * from e:\mbg\t22 where 手机号码not in (sele 手机号码from e:\mbg\t24 )
copy to e:\mbg\t25
close all
use e:\mbg\t24
append from e:\mbg\t25
sele a.*,b.bill_fee/100 as 账单0703月from e:\mbg\t24 a,e:\mbg\info0703 b where er_id=er_id
copy to e:\mbg\t26
sele * from e:\mbg\t24 where 手机号码not in (sele 手机号码from e:\mbg\t26 )
copy to e:\mbg\t27
close all
use e:\mbg\t26
append from e:\mbg\t27
copy to e:\mbg\全球通200元包月客户清单type xls
sele * from e:\mbg\info0802 where arrive_tag='1' and brand_code like 'G0%'
copy to e:\mbg\t1
sele * from e:\mbg\t1 where user_id in (sele user_id from e:\mbg\discnt0802 where discnt_cod=906)
copy to e:\mbg\t2
sele user_id,stat_month,develop_de,user_state,city_code as 业务区,serial_num as 手机号码,cust_name as 客户姓名,bill_fee/100 as 消费,in_date as 入网时间,home_addre as 家庭地址,contact_ph as 联系电话from e:\mbg\t2
copy to e:\mbg\t4
sele a.*,erstate as 状态from e:\mbg\t4 a,e:\mbg\userstate b where er_state=rstateco copy to e:\mbg\t5
select a.*,b.部门名称from e:\mbg\t5 a,e:\mbg\depart_id b where a.develop_de=b.部门标识copy to e:\mbg\t6
sele a.*,b.start_date as 优惠开始时,b.end_date as 优惠结束时from e:\mbg\t6 a,e:\mbg\discnt0802 b where er_id=er_id
sele a.*,b.start_date as 优惠开始时,b.end_date as 优惠结束时from e:\mbg\t6 a,e:\mbg\discnt0802 b where er_id=er_id and discnt_cod=906
copy to e:\mbg\全球通250元包月客户清单type xls
计算公话数据时核实报给我的清单是否正确
close all
use e:\mbg\公话新增清单0802
modify stru
repl all 公话号码with alltrim (公话号码)
sele a.* from e:\mbg\公话新增清单0802 a,e:\mbg\info0802 b where a.公话号码=b.serial_num
and arrive_tag='1' and public_tag in ('1','2','3') group by 公话号码
copy to e:\mbg\公话新增清单0802正确
sele * from e:\mbg\公话新增清单0802 where 公话号码not in (sele 公话号码from e:\mbg\公话新增清单0802正确)
copy to e:\mbg\非2月新增type xls
1月新增中特殊号码清单（不包括每个号段的ABCD的7个号码）
sele * from e:\mbg\info0801 where brand_code like 'G0%' and new_tag='1'
copy to e:\mbg\t2
sele * from e:\mbg\t2 where (substr(serial_num,8,1)=substr(serial_num,9,1) and substr(serial_num,9,1)=substr(serial_num,10,1) and substr(serial_num,10,1)=substr(serial_num,11,1)) or (substr(serial_num,8,1)=substr(serial_num,9,1) and substr(serial_num,9,1)=substr(serial_num,10,1)) or (substr(serial_num,8,1)=substr(serial_num,9,1) and substr(serial_num,10,1)=substr(serial_num,11,1)) or (substr(serial_num,9,1)=substr(serial_num,10,1) and substr(serial_num,10,1)=substr(serial_num,11,1))
copy to e:\mbg\1月办理特殊号码清单type xls
红名单和信用度清单：剔除公话和员工
close all
sele * from e:\mbg\info0803 where public_tag not in ('1','2','3') and prepay_tag='0' and brand_code like 'G0%' and serial_num not in (sele 手机号码from e:\mbg\公司员工)
copy to e:\mbg\t1
sele user_id,develop_de,user_state,city_code as 业务区,serial_num as 手机号码,cust_name as 客户姓名,in_date as 入网时间,red_tag as 红名单标志,prepay_tag as 后付费标志,bill_fee/100 as 本月消费,home_addre as 家庭地址,contact_ph as 联系电话from e:\mbg\t1
copy to e:\mbg\t2
sele a.*,erstate as 状态from e:\mbg\t2 a,e:\mbg\userstate b where er_state=rstateco copy to e:\mbg\t3
sele a.*,b.部门名称from e:\mbg\t3 a,e:\mbg\depart_id b where a.develop_de=b.部门标识copy to e:\mbg\t4
copy to e:\mbg\全州后付费清单type xls
close all
sele * from e:\mbg\info0803 where public_tag not in ('1','2','3') and prepay_tag<>'0' and credit_val>0 and arrive_tag='1' and brand_code like 'G0%' and serial_num not in (sele 手机号码from e:\mbg\公司员工)
copy to e:\mbg\t1
sele user_id,develop_de,user_state,city_code as 业务区,serial_num as 手机号码,cust_name as
客户姓名,in_date as 入网时间,red_tag as 红名单标志,prepay_tag as 后付费标志,credit_val as 信誉度,bill_fee/100 as 本月消费,home_addre as 家庭地址,contact_ph as 联系电话from e:\mbg\t1
copy to e:\mbg\t2
sele a.*,erstate as 状态from e:\mbg\t2 a,e:\mbg\userstate b where er_state=rstateco copy to e:\mbg\t3
sele a.*,b.部门名称from e:\mbg\t3 a,e:\mbg\depart_id b where a.develop_de=b.部门标识copy to e:\mbg\t4
copy to e:\mbg\全州信誉度客户清单type xls
提取号段下所有号码的优惠、彩铃信息、
sele * from e:\mbg\info0803 where arrive_tag='1' and (serial_num like '1389944%' or serial_num like '1356550%' or serial_num like '1367997%' or serial_num like '1377901%' or serial_num like '1500909%' or serial_num like '1580909%' or serial_num like '1590909%' or serial_num like '1589938%')
copy to e:\mbg\t1
sele user_id,serial_num as 客户号码,cust_name as 客户姓名,city_code as 业务区,in_date as 入网时间,age as 年龄,bill_fee/100 as 消费0803,bill_fee_g/100 as GPRS费用0803 ,substr(service_us,2,1) as 是否开彩铃from e:\mbg\t1
copy to e:\mbg\t2
sele a.*,b.bill_fee/100 as 消费0802,b.bill_fee_g /100 as GPRS费用02 from e:\mbg\t2 a,e:\mbg\info0802 b where er_id=er_id
copy to e:\mbg\t3
sele * from e:\mbg\t2 where user_id not in (sele user_id from e:\mbg\t3)
copy to e:\mbg\t4
close all
use e:\mbg\t3
append from e:\mbg\t4
close all
sele a.*,b.product_id as 产品标识,b.discnt_cod as 优惠编码from e:\mbg\t3 a,e:\mbg\discnt0803 b where er_id=er_id
copy to e:\mbg\外呼数据需求0428
copy to e:\mbg\外呼数据需求0428 type xls
copy to e:\mbg\外呼数据需求0428 deli with tab
copy to e:\mbg\外呼数据需求二0428 deli with tab for recno()>65535
sele * from e:\mbg\外呼数据需求0428 where 客户号码like '1389944%'
copy to e:\mbg\外呼数据需求1389944 type xls
sele * from e:\mbg\外呼数据需求0428 where 客户号码like '1356550%'
copy to e:\mbg\外呼数据需求1356550 type xls
sele * from e:\mbg\外呼数据需求0428 where 客户号码like '1367997%'
copy to e:\mbg\外呼数据需求1367997 type xls
sele * from e:\mbg\外呼数据需求0428 where 客户号码like '1377901%'
copy to e:\mbg\外呼数据需求1377901 type xls
sele * from e:\mbg\外呼数据需求0428 where 客户号码like '1500909%'
copy to e:\mbg\外呼数据需求1500909 type xls
copy to e:\mbg\外呼数据需求1500909 deli with tab
sele * from e:\mbg\外呼数据需求0428 where 客户号码like '1580909%'
copy to e:\mbg\外呼数据需求1580909 type xls
sele * from e:\mbg\外呼数据需求0428 where 客户号码like '1590909%'
copy to e:\mbg\外呼数据需求1590909 type xls
sele * from e:\mbg\外呼数据需求0428 where 客户号码like '1589938%'
copy to e:\mbg\外呼数据需求1589938 type xls
提取博乐城区手机号码清单
sele a.*,b.部门类型from e:\mbg\info0804 a,e:\mbg\2008年4月各营业部净增通话统计 b where a.city_code='E19A' and a.arrive_tag='1' and a.brand_code like 'G0%' and a.develop_de=b.部门标识
copy to e:\mbg\t1
sele serial_num as 手机号码from e:\mbg\t1 where 部门类型='城市特许专营店' or 部门类型='普通代理商' or 部门类型='直销渠道内部直销' or 部门类型='自办营业厅'
copy to e:\mbg\博乐城区手机号码type xls
copy to e:\mbg\博乐城区手机号码1 deli with tab
copy to e:\mbg\博乐城区手机号码2 deli with tab for recno()>65535
联通转网清单的联通号码在后几个月通话情况：
sele a.*,b.通话时长from e:\mbg\2008年高端手机转网清单（1-4月）.dbf a,e:\mbg\竞争对手查询0804_通话时长 b where a.CDMA手机号=b.号码
copy to e:\mbg\2008年高端手机转网清单（1-4月）_联通号码4月通话情况
sele * from e:\mbg\2008年高端手机转网清单（1-4月）.dbf where CDMA手机号not in (sele CDMA手机号from e:\mbg\2008年高端手机转网清单（1-4月）_联通号码4月通话情况) copy to e:\mbg\t1
close all
use e:\mbg\2008年高端手机转网清单（1-4月）_联通号码4月通话情况
append from e:\mbg\t1
close all
sele a.*,b.通话时长as 通话时长3 from e:\mbg\2008年高端手机转网清单（1-4月）_联通号码4月通话情况a,e:\mbg\竞争对手查询0803_通话时长 b where a.CDMA手机号=b.号码copy to e:\mbg\2008年高端手机转网清单（1-4月）_联通号码3月通话情况
sele a.*,b.通话时长as 通话时长3 from e:\mbg\2008年高端手机转网清单（1-4月）_联通号码4月通话情况a,e:\mbg\竞争对手查询0803_通话时长 b where a.CDMA手机号=b.号码copy to e:\mbg\2008年高端手机转网清单（1-4月）_联通号码3月通话情况
sele * from e:\mbg\2008年高端手机转网清单（1-4月）_联通号码4月通话情况where CDMA。