Selecting only specific columns
DB::select('column1','column2')->from('table_name'); SELECT `column1`, `column2` FROM `table_name` Selecting Column AS
DB::select(array('column','my_column'))->from('table_name')->compile($db); SELECT `column` AS `my_column` FROM `table_name` join()
DB::select()->from('table_name')->join('table_2')->on('table_2.table_id', '=', 'table_name.id');
SELECT * FROM `table_name` JOIN `table_2` ON `table_2`.`table_id` = `table_name`.`id` group_by() DB::select()->from('table_name')->group_by('column');
SELECT * FROM `table_name` GROUP BY `column`
DB::select()->from('table_name')->group_by(array('column1', 'mycol')); SELECT * FROM `table_name` GROUP BY `column1` AS `mycol`
having()
DB::select()->from('table_name')->having('column','=','value'); SELECT * FROM `table_name` HAVING `column` = 'value' and_having()
DB::select()->from('table_name')->having('column','=','value')->and_having('column2','=','value');
SELECT * FROM `table_name` HAVING `column` = 'value' AND `column2` = 'value' or_having()
DB::select()->from('table_name')->having('column','=','value')->or_having('column2','=','value'); SELECT * FROM `table_name` HAVING `column` = 'value' OR `column2` = 'value' having_open()
DB::select()->from('table_name')->having_open()->having('column','=','value') ->or_having('column2','=','value')->having_close();
SELECT * FROM `table_name` HAVING (`column` = 'value' OR `column2` = 'value')
and_having_open() DB::select()->from('table_name')->where('column','=','value')->and_having_open()->having('column2','=','value')
->and_having('column3','=','value')->and_having_close();
SELECT * FROM `table_name` WHERE `column` = 'value' HAVING (`column2` = 'value' AND `column3` = 'value') or_having_open() DB::select()->from('table_name')->where('column','=','value')->or_having_open()->having('column2','=','value')
->or_having('column3','=','value')->or_having_close();
SELECT * FROM `table_name` WHERE `column` = 'value' HAVING (`column2` = 'value' OR `column3` = 'value')
order_by()
DB::select()->from('table_name')->order_by('column', 'ASC'); SELECT * FROM `table_name` ORDER BY `column` ASC
limit()
DB::select()->from('table_name')->limit(10); SELECT * FROM `table_name` LIMIT 10
offset()
DB::select()->from('table_name')->limit(10)->offset(50); SELECT * FROM `table_name` LIMIT 10 OFFSET 50
where()
DB::select()->from('table_name')->where('column','=','value'); SELECT * FROM `table_name` WHERE `column` = 'value' and_where()
DB::select()->from('table_name')->where('column','=','value')->and_where('column2','=','value'); SELECT * FROM `table_name` WHERE `column` = 'value' AND `column2` = 'value' or_where()
DB::select()->from('table_name')->where('column','=','value')->or_where('column2','=','value'); SELECT * FROM `table_name` WHERE `column` = 'value' OR `column2` = 'value' where_open()
DB::select()->from('table_name')->where_open()->where('column','=','value') ->or_where('column2','=','value')->where_close();
SELECT * FROM `table_name` WHERE (`column` = 'value' OR `column2` = 'value') and_where_open() DB::select()->from('table_name')->where('column','=','value')->and_where_open()->where('column2','=','value')
->or_where('column3','=','value')->and_where_close();
SELECT * FROM `table_name` WHERE `column` = 'value' AND (`column2` = 'value' OR `column3` = 'value') or_where_open() DB::select()->from('table_name')->where('column','=','value')->or_where_open()->where('column2','=','value')
->and_where('column3','=','value')->or_where_close();
SELECT * FROM `table_name` WHERE `column` = 'value' OR (`column2` = 'value' AND `column3` = 'value')
第二篇：常用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\20xx年公话相关数据统计_原件.prg
5、各营业部净增通话：发电子邮
件
计算每月通话用户数_07年乡镇团场营业厅净增通话统计0802.PRG
计算每月净增通话统计表的指标语句.PRG
计算营业厅当月欠费回收率_20xx年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\20xx年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 b.user_id,a.* from e:\mbg\20xx年9月末梢代办放号清单 a,e:\mbg\info0709 b where a.手机号=b.serial_num and b.arrive_tag='1'
copy to e:\mbg\t0
sele a.*,b.user_state,b.debt_fee/100 as 往月欠费,stat_month as 统计月份 from e:\mbg\t0 a,e:\mbg\info0712 b where a.user_id=b.user_id order by 业务区,部门,代销商
copy to e:\mbg\t1
select a.*,b.userstate as 状态 from e:\mbg\t1 a,e:\mbg\userstate b where a.user_state=b.usrstateco
copy to e:\mbg\20xx年9月末梢代办放号清单_在第4个月清单
copy to e:\mbg\20xx年9月末梢代办放号清单_在第4个月清单 type xls
sele 业务区,部门,代销商,count(*) as 在网数 from e:\mbg\20xx年9月末梢代办放号清单_在第4个月清单 where 状态 in ('开通','高额停机') and 往月欠费=0 group by 业务区,部门,代销商 order by 业务区,部门
copy to e:\mbg\20xx年9月末梢代办放号清单_在第4个月在网汇总 type xls
sele b.user_id,a.* from e:\mbg\20xx年9月营销员放号清单.dbf a,e:\mbg\info0709 b where a.手机号=b.serial_num and b.arrive_tag='1'
copy to e:\mbg\t0
sele a.*,b.user_state,b.debt_fee as 往月欠费,stat_month as 统计月份 from e:\mbg\t0 a,e:\mbg\info0712 b where a.user_id=b.user_id order by 业务区,营销员姓名
copy to e:\mbg\t1
select a.*,b.userstate as 状态 from e:\mbg\t1 a,e:\mbg\userstate b where a.user_state=b.usrstateco
copy to e:\mbg\20xx年9月营销员放号清单_在第4个月清单
copy to e:\mbg\20xx年9月营销员放号清单_在第4个月清单 type xls
sele 业务区,营销员姓名,count(*) as 在网数 from e:\mbg\20xx年9月营销员放号清单_在第4个月清单 where 状态 in ('开通','高额停机') and 往月欠费=0 group by 业务区,营销员姓名 order by 业务区,营销员姓名
copy to e:\mbg\20xx年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.*,b.userstate from e:\mbg\t1 a,e:\mbg\userstate b where a.状态=b.usrstateco
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 a.user_id=b.user_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 a.user_id=b.user_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 a.user_id=b.user_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
copy to e:\mbg\t7
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
发邮件给
20xx年9月营销员放号清单_在第4个月在网汇总.XLS20xx年9月末梢代办放号清单_在第4个月在网汇总.XLS
发邮件给
20xx年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.*,b.userstate as 状态 from e:\mbg\t1 a,e:\mbg\userstate b where a.user_state=b.usrstateco
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\欠费在300元以上用户清单
sele b.stat_month as 统计月份,a.*,b.debt_fee/100 as 欠费 from e:\mbg\欠费在300元以上用户清单 a,e:\mbg\info0802 b where a.user_id=b.user_id
copy to e:\mbg\欠费在300元以上用户清单 type xls
是否加六元数据营销包？
sele * from e:\mbg\2月清单汇总 group by 手机号码
sele a.*,b.user_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 a.user_id=b.user_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 a.user_id=b.user_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.FEE_INFO,b.FEE_CMNT,b.FEE_OTHE,b.FEE_SMS from e:\mbg\t1 a,e:\mbg\debt0802 b where a.user_id=b.user_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 a.user_id=b.user_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.*,b.userstate as 状态 from e:\mbg\t1 a,e:\mbg\userstate b where a.状态=b.usrstateco copy to e:\mbg\大河沿和哈镇手机号码 type xls
1、未开通彩铃的客户；2、消费在50元以上；3、入网时间在20xx年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 a.user_id=b.user_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 a.user_id=b.user_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 a.user_id=b.user_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.*,b.userstate as 状态 from e:\mbg\t1 a,e:\mbg\userstate b where a.user_state=b.usrstateco copy to e:\mbg\20xx年2月销号客户清单 deli with tab
copy to e:\mbg\20xx年2月销号客户清单 type xls
*!* 统计20xx年12月份通话客户在20xx年2月份未通话的客户清单
modify command e:\mbg\统计20xx年12月份通话客户在20xx年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.*,b.userstate as 用户状态 from f:\mbg\t2 a,f:\mbg\userstate b where a.user_state=b.usrstateco into cursor s1
copy to f:\mbg\20xx年12月份通话客户在20xx年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.*,b.userstate as 状态 from e:\mbg\t1 a,e:\mbg\userstate b where a.user_state=b.usrstateco 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\20xx年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.*,b.userstate as 状态 from e:\mbg\t4 a,e:\mbg\userstate b where a.user_state=b.usrstateco 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 a.user_id=b.user_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.*,b.userstate as 状态 from e:\mbg\t2 a,e:\mbg\userstate b where a.user_state=b.usrstateco
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.*,b.userstate as 状态 from e:\mbg\t4 a,e:\mbg\userstate b where a.user_state=b.usrstateco 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
a.user_id=b.user_id
copy to e:\mbg\t7
sele a.*,b.bill_fee/100 as 账单0712月 from e:\mbg\t6 a,e:\mbg\info0712 a.user_id=b.user_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 a.user_id=b.user_id
close all
sele a.*,b.bill_fee/100 as 账单0712月 from e:\mbg\t7 a,e:\mbg\info0712 a.user_id=b.user_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 a.user_id=b.user_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 a.user_id=b.user_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 b where b where b where b where b where
sele a.*,b.bill_fee/100 as 账单0709月 from e:\mbg\t12 a,e:\mbg\info0709 a.user_id=b.user_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 a.user_id=b.user_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 a.user_id=b.user_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 a.user_id=b.user_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 a.user_id=b.user_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 a.user_id=b.user_id
copy to e:\mbg\t24 b where b where b where b where b where b where
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 a.user_id=b.user_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.*,b.userstate as 状态 from e:\mbg\t4 a,e:\mbg\userstate b where a.user_state=b.usrstateco 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 a.user_id=b.user_id
sele a.*,b.start_date as 优惠开始时,b.end_date as 优惠结束时 from e:\mbg\t6 a,e:\mbg\discnt0802 b where a.user_id=b.user_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.*,b.userstate as 状态 from e:\mbg\t2 a,e:\mbg\userstate b where a.user_state=b.usrstateco 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.*,b.userstate as 状态 from e:\mbg\t2 a,e:\mbg\userstate b where a.user_state=b.usrstateco 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 a.user_id=b.user_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 a.user_id=b.user_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\20xx年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\20xx年高端手机转网清单（1-4月）.dbf a,e:\mbg\竞争对手查询0804_通话时长 b where a.CDMA手机号=b.号码
copy to e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码4月通话情况
sele * from e:\mbg\20xx年高端手机转网清单（1-4月）.dbf where CDMA手机号 not in (sele CDMA手机号 from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码4月通话情况) copy to e:\mbg\t1
close all
use e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码4月通话情况
append from e:\mbg\t1
close all
sele a.*,b.通话时长 as 通话时长3 from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码4月通话情况 a,e:\mbg\竞争对手查询0803_通话时长 b where a.CDMA手机号=b.号码 copy to e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码3月通话情况
sele a.*,b.通话时长 as 通话时长3 from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码4月通话情况 a,e:\mbg\竞争对手查询0803_通话时长 b where a.CDMA手机号=b.号码 copy to e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码3月通话情况
sele * from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码4月通话情况 where CDMA
手机号 not in (sele CDMA手机号 from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码3月通话情况)
copy to e:\mbg\t2
close all
use e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码3月通话情况
append from e:\mbg\t2
close all
sele a.*,b.通话时长 as 通话时长2 from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码3月通话情况 a,e:\mbg\竞争对手查询0802_通话时长 b where a.CDMA手机号=b.号码 copy to e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码2月通话情况
sele * from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码3月通话情况 where CDMA手机号 not in (sele CDMA手机号 from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码2月通话情况)
copy to e:\mbg\t3
close all
use e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码2月通话情况
append from e:\mbg\t3
close all
sele a.*,b.通话时长 as 通话时长1 from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码2月通话情况 a,e:\mbg\竞争对手查询0801_通话时长 b where a.CDMA手机号=b.号码 copy to e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码1月通话情况
sele * from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码2月通话情况 where CDMA手机号 not in (sele CDMA手机号 from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码1月通话情况)
copy to e:\mbg\t4
close all
use e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码1月通话情况
append from e:\mbg\t4
close all
sele * from e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码1月通话情况
copy to e:\mbg\20xx年高端手机转网清单（1-4月）_联通号码1-4月通话情况 type xls
2月漫游到灾区客户仍有欠费客户清单
sele a.*,b.user_id,b.develop_de,b.city_code,b.prepay_tag from e:\mbg\博州漫游到灾区客户.dbf a,e:\mbg\info0802 b where a.手机号码=b.serial_num and b.arrive_tag='1'
copy to e:\mbg\t1
sele a.*,b.user_state from e:\mbg\t1 a,e:\mbg\info0804 b where a.user_id=b.user_id copy to e:\mbg\t2
sele a.*,b.fee_sum as 欠费 from e:\mbg\t2 a,e:\mbg\debt0804 b where a.user_id=b.user_id and ( debt_bcyc_=200802 or (a.prepay_tag='1' and debt_bcyc_=200803)) group by serial_num
copy to e:\mbg\t3
sele a.*,b.userstate as 状态 from e:\mbg\t3 a,e:\mbg\userstate b where a.user_state=b.usrstateco
copy to e:\mbg\t4
sele a.*,b.部门名称 from e:\mbg\t4 a,e:\mbg\depart_id b where a.develop_de=b.部门标识 copy to e:\mbg\2月漫游到灾区客户仍有欠费客户清单 type xls
欠费3个月以上用户清单
sele city_code as 业务区,serial_num as 手机号,cust_name as 客户名,develop_de,user_state,BRAND_CODE as 品牌,in_date as 开户时间,bill_fee/100 as 本月应收,prepay_tag as 预付费标志,red_tag as 红名单标志,credit_val/100 as 信用度,pay_mode_c as 付费方式,public_tag as 公话标志,DEBT_FEE/100 as 欠费金额,MIN_DEBT_B as 最早欠月份,call_time as 通话时长,last_stop_ as 最后停机时间,home_addre as 家庭住址,contact_ph as 联系电话 from e:\mbg\info0804 where DEBT_FEE>0
copy to e:\mbg\t1
sele * from e:\mbg\t1 where 最早欠月份<=200712 and 品牌 like 'G0%'
copy to e:\mbg\t2
sele * from e:\mbg\t2 group by 手机号
sele * from e:\mbg\t2 order by 手机号
select a.*,b.userstate as 用户状态 from e:\mbg\t2 a,e:\mbg\userstate b where a.user_state=b.usrstateco
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\t4
sele * from e:\mbg\t3 where develop_de not in (sele develop_de from e:\mbg\t4)
copy to e:\mbg\t5
close all
use e:\mbg\t4
append from e:\mbg\t5
copy to e:\mbg\全州欠费3个月以上用户清单 type xls
copy to e:\mbg\全州欠费3个月以上用户清单 deli with tab
copy to e:\mbg\全州欠费3个月以上用户清单2 deli with tab for reco()>65535
copy to e:\mbg\全州欠费3个月以上用户清单2 deli with tab for recno()>65535
张英(张英) 18:13:44
已经给你们倒到查询服务器中了
select * from ts_mxh_addcredit_user
截止20xx年5月31日精河业务区开通彩铃用户数，剔除奥运88
套餐，编码（20080101、20080102、20080103、20080104、20080105、20080106、20080107）五元数据营销包（90990092）六元数据营销包（90990093、90990094）大众卡20元月租送70分钟本地主叫及彩铃90990188,5月消费在100元（含）以上
sele * from e:\mbg\info0805 where city_code='E19B' and arrive_tag='1' and substr(SERVICE_US,2,1)<>'0' and bill_fee/100>=100
copy to e:\mbg\t1
sele * from e:\mbg\t1 where serial_num not in (sele serial_num from e:\mbg\discnt0805 where discnt_cod=20080101 or discnt_cod=20080102 or discnt_cod=20080103 or discnt_cod=20080104 or discnt_cod=20080105 or discnt_cod=20080106 or discnt_cod=20080107 or discnt_cod=90990092 or discnt_cod=90990093 or discnt_cod=90990094 or discnt_cod=90990188)
copy to e:\mbg\t2
sele user_id,develop_de,serial_num as 客户号码,cust_name as 客户姓名,city_code as 业务区,in_date as 入网时间,bill_fee/100 as 消费 from e:\mbg\t2
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\精河开通彩铃用户清单 type xls
200806当月欠费明细
sele a.user_id,a.serial_num as 手机号码,a.city_code as 业务区,a.develop_de,a.brand_code,a.in_date as 开户时间,a.prepay_tag as 预付费标志,a.red_tag as 红名单标志,b.public_tag as 公话标志,b.credit_val as 信誉度,b.vip_tag,a.fee_sum as 总欠费,a.fee_base as 月租费,a.fee_call as 通话费,a.fee_opfc as 新业务费,a.fee_data as 数据业务费,a.fee_info as 信息费,a.fee_CMNT as CMNET费,a.fee_othe as 其他费,a.fee_sms as 短信费,a.fee_call_1 as 本地通话费,a.fee_call_2 as 国内长途,a.fee_call_4 as 漫游费 from e:\mbg\debt0806 a,e:\mbg\info0806 b where a.user_id=b.user_id and a.debt_bcyc_=200805 and a.fee_sum>0 copy to e:\mbg\t1
sele a.*,b.vip_name as 卡类级别 from e:\mbg\t1 a,e:\mbg\td_m_vipclass b where a.vip_tag=b.vip_tag
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
append from e:\mbg\t3
copy to e:\mbg\6月当月欠费明细表 type xls
6月新入网的客户身份证号码在网状态下的所有相同身份证、客户姓名办理的手机号码 备注6月之前此证无号码
sele user_id,pspt_id as 身份证号码,cust_name as 客户姓名 from e:\mbg\info0806 where in_date>={^2008-06-01} and in_date<={^2008-06-30} and brand_code like 'G0%'
copy to e:\mbg\6月新增
sele * from e:\mbg\6月新增 where 身份证号码 not in (sele pspt_id from e:\mbg\info0805)
copy to e:\mbg\6月新增中此证6月前无号码
sele * from e:\mbg\6月新增 where 客户姓名 not in (sele cust_name from e:\mbg\info0805)
copy to e:\mbg\6月新增中此姓名6月前无号码
close all
sele user_id,develop_de,user_state,city_code as 业务区,serial_num as 手机号码,cust_name as 客户姓名,pspt_id as 身份证号码,in_date as 入网时间,last_stop_ as 最后停机时间 from e:\mbg\info0807 where arrive_tag='1' and brand_code like 'G0%' and pspt_id in (sele 身份证号码 from e:\mbg\6月新增)
copy to e:\mbg\t1
sele a.*,b.客户自缴金,b.公司贴补金 from e:\mbg\t1 a,e:\mbg\电子钱包收费明细表06.DBF b where a.手机号码=b.号码
copy to e:\mbg\t2
close all
sele * from e:\mbg\t1 where 手机号码 not in (sele 手机号码 from e:\mbg\t2)
copy to e:\mbg\t3
close all
use e:\mbg\t2
append from e:\mbg\t3
close all
sele a.*,b.身份证号码 from e:\mbg\t2 a,e:\mbg\6月新增中此证6月前无号码 b where a.user_id=b.user_id
copy to e:\mbg\t4
sele * from e:\mbg\t2 where user_id not in (sele user_id from e:\mbg\t4)
copy to e:\mbg\t5
close all
use e:\mbg\t4
append from e:\mbg\t5
close all
sele * from e:\mbg\t4
close all
sele a.*,b.userstate as 状态 from e:\mbg\t4 a,e:\mbg\userstate b where a.user_state=b.usrstateco
copy to e:\mbg\t6
select a.*,b.部门名称 from e:\mbg\t6 a,e:\mbg\depart_id b where a.develop_de=b.部门标识
copy to e:\mbg\6月新增中身份证相同号码清单 deli with tab
close all
sele a.*,b.身份证号码 from e:\mbg\t2 a,e:\mbg\6月新增中此姓名6月前无号码 b where a.user_id=b.user_id
copy to e:\mbg\t4
sele * form e:\mbg\t2 where user_id not in (sele user_id from e:\mbg\t4) sele * from e:\mbg\t2 where user_id not in (sele user_id from e:\mbg\t4) copy to e:\mbg\t5
close all
use e:\mbg\t4
append from e:\mbg\t5
close all
sele * from e:\mbg\t4
close all
sele a.*,b.userstate as 状态 from e:\mbg\t4 a,e:\mbg\userstate b where a.user_state=b.usrstateco
copy to e:\mbg\t6
select a.*,b.部门名称 from e:\mbg\t6 a,e:\mbg\depart_id b where a.develop_de=b.部门标识
copy to e:\mbg\6月新增中身份证相同号码清单_客户姓名 deli with tab copy to e:\mbg\6月新增中身份证相同号码清单_客户姓名 type xls
连续7个月消费在120元以上用户清单
sele user_id,city_code as 业务区,develop_de,serial_num as 手机号码,cust_name as 客户姓名,in_date as 开户时间,brand_code as 品牌1月,bill_fee/100 as 费用1 from e:\mbg\info0801 where arrive_tag='1' and (brand_code='G001' or brand_code='G002') and bill_fee/100>=120 copy to e:\mbg\t1
sele a.*,b.bill_fee/100 as 费用2 from e:\mbg\t1 a,e:\mbg\info0802 b where a.user_id=b.user_id and b.bill_fee/100>=120
copy to e:\mbg\t2
sele a.*,b.bill_fee/100 as 费用3 from e:\mbg\t2 a,e:\mbg\info0803 b where a.user_id=b.user_id and b.bill_fee/100>=120
copy to e:\mbg\t3
sele a.*,b.bill_fee/100 as 费用4 from e:\mbg\t3 a,e:\mbg\info0804 b where a.user_id=b.user_id and b.bill_fee/100>=120
copy to e:\mbg\t4
sele a.*,b.bill_fee/100 as 费用5 from e:\mbg\t4 a,e:\mbg\info0805 b where a.user_id=b.user_id and b.bill_fee/100>=120
copy to e:\mbg\t5
sele a.*,b.bill_fee/100 as 费用6 from e:\mbg\t5 a,e:\mbg\info0806 b where a.user_id=b.user_id and b.bill_fee/100>=120
copy to e:\mbg\t6
sele a.*,b.bill_fee/100 as 费用7,b.brand_code as 品牌7月b.user_state,b.vip_tag from e:\mbg\t6 a,e:\mbg\info0807 b where a.user_id=b.user_id and b.bill_fee/100>=120
copy to e:\mbg\t7
sele a.*,b.部门名称 from e:\mbg\t7 a,e:\mbg\depart_id b where a.develop_de=b.部门标识
copy to e:\mbg\t8
sele a.*,b.userstate as 状态 from e:\mbg\t8 a,e:\mbg\userstate b where a.user_state=b.usrstateco
copy to e:\mbg\t9
sele * from e:\mbg\td_m_vipclass.DBF
sele a.*,b.vip_name from e:\mbg\t9 a,e:\mbg\td_m_vipclass.DBF b where a.vip_tag=b.vip_tag
copy to e:\mbg\t10
sele * from e:\mbg\t9 wher 手机号码 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 * form e:\mbg\t10
sele * from e:\mbg\t10
copy to e:\mbg\1-7月连续消费在120元以上的用户清单 type xls
sele a.* from e:\mbg\2-7月连续消费在120元以上的用户清单.DBF a,e:\mbg\info0807 b where a.user_id=b.user_id and b.pay_mode_c<>'1' copy to e:\mbg\2-7月连续消费在120元以上的用户清单剔除托收
sele a.* from e:\mbg\2-7月连续消费在120元以上的用户清单剔除托收 a,e:\mbg\info0807 b where a.user_id=b.user_id having count(*)<2 group by acct_id
copy to e:\mbg\消费在120元以上用户清单（剔除托收、合帐）
全球通剔除党政、大帐、电子钱包数据
sele user_id,serial_num from e:\mbg\info0807 where brand_code='G001' and arrive_tag='1' having count(*)<2 group by acct_id
copy to e:\mbg\t1
sele * from e:\mbg\t1 where serial_num not in (sele 手机号码 from e:\mbg\电子钱包清单0807)
copy to e:\mbg\t2
use e:\mbg\党政机关
modify struc
repl all 手机号码 with alltrim(手机号码)
close all
sele * from e:\mbg\t2 where serial_num not in (sele 手机号码 from e:\mbg\党政机关.dbf)
copy to e:\mbg\全球通剔除党政、合帐、电子钱包
copy to e:\mbg\全球通剔除党政、合帐、电子钱包 type xls
每月新增客户收入
sele user_id,city_code,serial_num,brand_code,bill_fee/100 as 费用0807 from e:\mbg\info0807 where arrive_tag='1' and brand_code like 'G0%' and in_date>={^2008-07-01} and in_date<={^2008-07-31}
copy to e:\mbg\t1
sele city_code,brand_code,count(*),sum(费用0807) from e:\mbg\t1 group by brand_code,city_code
copy to e:\mbg\7月新增收入
copy to e:\mbg\7月新增收入 type xls
sele user_id,city_code,serial_num,brand_code,bill_fee/100 as 费用0806 from e:\mbg\info0806 where arrive_tag='1' and brand_code like 'G0%' and in_date>={^2008-06-01} and in_date<={^2008-06-30}
copy to e:\mbg\t2
sele city_code,brand_code,count(*),sum(费用0806) from e:\mbg\t2 group by brand_code,city_code
copy to e:\mbg\6月新增收入
copy to e:\mbg\6月新增收入 type xls
sele user_id,city_code,serial_num,brand_code,bill_fee/100 as 费用0805 from e:\mbg\info0805 where arrive_tag='1' and brand_code like 'G0%' and in_date>={^2008-05-01} and in_date<={^2008-05-31}
copy to e:\mbg\t3
sele city_code,brand_code,count(*),sum(费用0805) from e:\mbg\t3 group by brand_code,city_code
copy to e:\mbg\5月新增收入
copy to e:\mbg\5月新增收入 type xls
sele user_id,city_code,serial_num,brand_code,bill_fee/100 as 费用0804 from e:\mbg\info0804 where arrive_tag='1' and brand_code like 'G0%' and in_date>={^2008-04-01} and in_date<={^2008-04-30}
copy to e:\mbg\t4
sele city_code,brand_code,count(*),sum(费用0804) from e:\mbg\t4 group by brand_code,city_code
copy to e:\mbg\4月新增收入
copy to e:\mbg\4月新增收入 type xls
sele user_id,city_code,serial_num,brand_code,bill_fee/100 as 费用0803 from e:\mbg\info0803 where arrive_tag='1' and brand_code like 'G0%' and in_date>={^2008-03-01} and in_date<={^2008-03-31}
copy to e:\mbg\t5
sele city_code,brand_code,count(*),sum(费用0803) from e:\mbg\t5 group by brand_code,city_code
copy to e:\mbg\3月新增收入
copy to e:\mbg\3月新增收入 type xls
sele user_id,city_code,serial_num,brand_code,bill_fee/100 as 费用0802 from e:\mbg\info0802 where arrive_tag='1' and brand_code like 'G0%' and in_date>={^2008-02-01} and in_date<={^2008-02-29}
copy to e:\mbg\t6
sele city_code,brand_code,count(*),sum(费用0802) from e:\mbg\t6 group by brand_code,city_code
copy to e:\mbg\2月新增收入
copy to e:\mbg\2月新增收入 type xls
sele user_id,city_code,serial_num,brand_code,bill_fee/100 as 费用0801 from e:\mbg\info0801 where arrive_tag='1' and brand_code like 'G0%' and in_date>={^2008-01-01} and in_date<={^2008-01-31}
copy to e:\mbg\t7
sele city_code,brand_code,count(*),sum(费用0801) from e:\mbg\t7 group by brand_code,city_code
copy to e:\mbg\1月新增收入
copy to e:\mbg\1月新增收入 type xls
提取有价值客户清单(剔除随E卡、公话、托收、合帐、电子钱包、公司员工、前期营销活动)
sele user_id,city_code as 业务区,develop_de,user_state,public_tag,pay_mode_c,acct_id,serial_num as 手机号码,cust_name as 客户姓名,in_date as 开户时间,brand_code as 品牌,home_addre as 家庭地址,contact_ph as 联系电话 from e:\mbg\info0809 where arrive_tag='1' and (user_state='0' or user_state='7') and public_tag not in ('1','2','3') and pay_mode_c<>'1' and brand_code like 'G0%' and brand_code<>'G006' having count(*)<2 group by acct_id
copy to e:\mbg\t1
sele a.*,b.bill_fee/100 as 费用0807 from e:\mbg\t1 a,e:\mbg\info0807 b where a.user_id=b.user_id and b.arrive_tag='1'
copy to e:\mbg\t2
sele a.*,b.bill_fee/100 as 费用0806 from e:\mbg\t2 a,e:\mbg\info0806 b where a.user_id=b.user_id and b.arrive_tag='1'
copy to e:\mbg\t3
sele a.*,b.bill_fee/100 as 费用0805 from e:\mbg\t3 a,e:\mbg\info0805 b where a.user_id=b.user_id and b.arrive_tag='1'
copy to e:\mbg\t4
sele * from e:\mbg\t4 where (费用0805+费用0806+费用0807)>=240 copy to e:\mbg\5-7大于240元
select a.*,b.部门名称 from e:\mbg\5-7大于240元 a,e:\mbg\depart_id b where a.develop_de=b.部门标识
copy to e:\mbg\t6
close all
SELE * from e:\mbg\5-7大于240元 where 手机号码 not in (sele 手机号码 from e:\mbg\t6)
copy to e:\mbg\t7
close all
use e:\mbg\t6
append from e:\mbg\t7
close all
sele * from e:\mbg\t6 wher 手机号码 not in (sele 手机号码 from e:\mbg\公司员工)
copy to e:\mbg\5-7月连续消费在240元以上用户清单剔除公司员工、公话、电子钱包 deli with tab
copy to e:\mbg\t10
sele * from e:\mbg\t10 where (费用0805+费用0806+费用0807)>=360 copy to e:\mbg\5-7月连续消费在360元以上用户清单 type xls
sele * from e:\mbg\t8 where 手机号码 not in (sele 号码 from e:\mbg\电子钱包收费明细表0809.dbf)
copy to e:\mbg\t9
sele * from e:\mbg\t9 where 手机号码 not in (sele 号码 from e:\mbg\电子钱包收费明细表10.1-6.dbf)
copy to e:\mbg\t10
sele * from e:\mbg\t10 where 手机号码 not in (sele 手机号码 from e:\mbg\营销中心集团统付费汇总.dbf)
copy to e:\mbg\t11
sele * from e:\mbg\t11 where 手机号码 not in (sele 手机号码 from e:\mbg\全州党政机关.dbf)
copy to e:\mbg\t12
sele * from e:\mbg\t12 where 手机号码 not in (sele 手机号码 from e:\mbg\电子钱包清单0809.dbf)
copy to e:\mbg\t13
close all
sele * from e:\mbg\t13 where 手机号码 not in (sele serial_num from
e:\mbg\新闻早晚报.DBF)
copy to e:\mbg\t1
sele * from e:\mbg\t1 where 手机号码 not in (sele serial_num from e:\mbg\彩铃.DBF)
copy to e:\mbg\t2
sele * from e:\mbg\t2 where 手机号码 not in (sele serial_num from e:\mbg\来电管家.DBF)
copy to e:\mbg\t3
sele * from e:\mbg\t3 where 手机号码 not in (sele serial_num from e:\mbg\数据营销包.DBF)
copy to e:\mbg\有价值客户清单1008
copy to e:\mbg\有价值客户清单1008 deli with tab
copy to e:\mbg\有价值客户清单1008 type xls
select a.user_id,a.serial_num,b.优惠名,b.打折率,a.END_DATE from e:\mbg\discnt0809 a,e:\mbg\打折包月优惠 b wher a.DISCNT_COD=b.优惠编码
sele city_code,develop_de,serial_num,bill_fee/100 from e:\mbg\info0809 where bill_fee/100=72.98 and call_sums>0 and public_tag in ('1','2','3') and serial_num not in (sele serial_num from e:\mbg\info0810 where call_sums>0)
博乐欠费100元以上用户清单：
sele user_id,serial_num as 手机号码,cust_name as 客户姓名,debt_fee/100 as 欠费,user_state,home_addre as 地址,contact_ph as 联系电话 from e:\mbg\info0810 where debt_fee/100>=100 and city_code='E19A' and brand_code like 'G0%' and min_debt_b>=200502 and min_debt_b<=200711
bosssta1 @bosssta1
uif_sta_19
uifsta19789
统计生日：
select serial_num,pspt_id,cust_name,user_state,public_tag from
e:\mbg\info0811 where (substr(pspt_id,11,4)='0920' or substr(pspt_id,9,4)='0920') and (user_state='0'or user_state='7') and brand_code like 'G0%'
select serial_num,pspt_id,cust_name,user_state,public_tag from e:\mbg\info0811 where (substr(pspt_id,11,4)='0920' or substr(pspt_id,9,4)='0920') and (user_state='0'or user_state='7') and brand_code like 'G0%' and public_tag in ('1','2','3')
提取在网10年以上用户清单剔除公司员工: 在19xx年12月31日前入网，截止20xx年12月仍在网的用户清单，剔除公司员工
sele user_id,user_state,city_code as 业务区,serial_num as 手机号
码,cust_name as 客户名称,brand_code as 品牌,vip_tag,in_date as 入网时间 from e:\mbg\info0812 where arrive_tag='1' and in_date<={^1999-12-31} copy to e:\mbg\t1
sele a.*,b.userstate as 状态 from e:\mbg\t1 a,e:\mbg\userstate b where a.user_state=b.usrstateco
copy to e:\mbg\t2
sele * from e:\mbg\t2 where 手机号码 not in (sele 手机号码 from e:\mbg\公司员工)
copy to e:\mbg\t3
sele a.*,b.是否电子钱,b.到期日 from e:\mbg\t3 a,e:\mbg\11月电子钱包清单 b where a.手机号码=b.手机号码
copy to e:\mbg\t4
sele * from e:\mbg\t3 where 手机号码 not in (sele 手机号码 from e:\mbg\t4)
copy to e:\mbg\t5
close all
use e:\mbg\t4
append from e:\mbg\t5
close all
sele a.*,b.受理时间 from e:\mbg\t4 a,e:\mbg\电子钱包收费明细表11-12月.DBF b where a.手机号码=b.手机号码
copy to e:\mbg\t6
sele * from e:\mbg\t4 where 手机号码 not in (sele 手机号码 from e:\mbg\t6)
copy to e:\mbg\t7
close all
use e:\mbg\t6
append from e:\mbg\t7
close all
sele a.*,b.vip_name as 卡类级别 from e:\mbg\t6 a,e:\mbg\td_m_vipclass b where a.vip_tag=b.vip_tag
copy to e:\mbg\t8
sele * from e:\mbg\t6 where 手机号码 not in (sele 手机号码 from e:\mbg\t8)
copy to e:\mbg\t9
close all
use e:\mbg\t8
append from e:\mbg\t9
close all
sele * from e:\mbg\t8
copy to e:\mbg\20xx年前在网用户清单
copy to e:\mbg\20xx年前在网用户清单 type xls
提取中高端客户清单：
sele
user_id,PRODUCT_ID,DEVELOP_DE,USER_STATE,VIP_TAG,PUBLIC_TAG,city_code as 业务区,serial_num as 手机号码,cust_name as 客户名称,brand_code as 品牌,in_date as 开户时间,PAY_MODE_C as 付费方式,PREPAY_TAG as 预付费标志,RED_TAG as 红名单标志,HOME_ADDRE as 家庭地址,CONTACT as 联系人姓名,CONTACT_PH as 联系人电话,bill_fee/100 as 消费0812 from e:\mbg\info0812 where arrive_tag='1' and brand_code like 'G0%'
copy to e:\mbg\t1
sele a.*,b.bill_fee/100 as 消费0811 from e:\mbg\t1 a,e:\mbg\info0811 b where a.user_id=b.user_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
append from e:\mbg\t3
close all
sele a.*,b.bill_fee/100 as 消费0810 from e:\mbg\t2 a,e:\mbg\info0810 b where a.user_id=b.user_id
copy to e:\mbg\t4
sele * from e:\mbg\t2 where user_id not in (sele user_id from e:\mbg\t4)
copy to e:\mbg\t5
close all
use e:\mbg\t4
append from e:\mbg\t5
close all
sele * from e:\mbg\t4
close all
sele a.*,b.userstate as 状态 from e:\mbg\t4 a,e:\mbg\userstate b where a.user_state=b.usrstateco
copy to e:\mbg\t6
sele a.*,b.部门名称 from e:\mbg\t6 a,e:\mbg\depart_id b where a.develop_de=b.部门标识 copy to e:\mbg\t7
sele * from e:\mbg\t6 where user_id not in (sele user_id from e:\mbg\t7)
copy to e:\mbg\t8
close all
use e:\mbg\t7
append from e:\mbg\t8
close all
sele a.*,b.vip_name as 卡类级别 from e:\mbg\t7 a,e:\mbg\td_m_vipclass a.vip_tag=b.vip_tag
copy to e:\mbg\t9
sele * from e:\mbg\t7 where user_id not in (sele user_id from e:\mbg\t9)
copy to e:\mbg\t10
close all
use e:\mbg\t9
append from e:\mbg\t10
close all
sele * from e:\mbg\产品参数表.DBF
sele a.*,b.产品名称 from e:\mbg\t9 a,e:\mbg\产品参数表.DBF
a.PRODUCT_ID=b.产品编码
copy to e:\mbg\t11
sele * from e:\mbg\t9 where user_id not in (sele user_id from e:\mbg\t11)
copy to e:\mbg\t12
close all
use e:\mbg\t11
append from e:\mbg\t12
close all
sele * from e:\mbg\t12
close all
sele * from e:\mbg\产品参数表.DBF
copy to e:\mbg\产品参数表 type xls
sele * from e:\mbg\t12
close all
use e:\mbg\产品参数表.DBF
modify struc
close all
sele * from e:\mbg\产品参数表.DBF
close all
sele a.*,b.产品名称 from e:\mbg\t9 a,e:\mbg\产品参数表.DBF
a.PRODUCT_ID=b.产品编码
copy to e:\mbg\t11
sele * from e:\mbg\t9 where user_id not in (sele user_id from e:\mbg\t11)
copy to e:\mbg\t12 b where b where b where
sele * from e:\mbg\t12 group by PRODUCT_ID
close all
sele a.*,b.产品名称 from e:\mbg\t9 a,e:\mbg\产品参数表.DBF b where a.PRODUCT_ID=b.产品编码
copy to e:\mbg\t11
sele * from e:\mbg\t9 where user_id not in (sele user_id from e:\mbg\t11)
copy to e:\mbg\t12
close all
use e:\mbg\t11
append from e:\mbg\t12
close all
sele * from e:\mbg\t11
close all
sele * from e:\mbg\公司员工
close all
use e:\mbg\公司员工
modify struc
repl all 号码 with alltrim(号码)
close all
sele a.*,b.公司员工 from e:\mbg\t11 a,e:\mbg\公司员工.DBF b where a.手机号码=b.号码 copy to e:\mbg\t13
sele * from e:\mbg\t11 where user_id not in (sele user_id from e:\mbg\t13)
copy to e:\mbg\t14
close all
use e:\mbg\t13
append from e:\mbg\t14
close all
sele * from e:\mbg\t13
copy to e:\mbg\10-12月用户清单
sele * from e:\mbg\10-12月用户清单 where 消费0812+消费0811+消费0810>=360 copy to e:\mbg\10-12月消费大于360元用户清单
sele a.*,b.集团客户编,b.集团名称,b.客户经理 as 集团经理 from e:\mbg\10-12月消费大于360元用户清单 a,e:\mbg\20xx年拍照集团成员填报模板_捆绑信息.dbf b where a.手机号码=b.手机号码
copy to e:\mbg\t1
sele * from e:\mbg\10-12月消费大于360元用户清单 wher 手机号码 not in (sele 手机号码 from e:\mbg\t1)
copy to e:\mbg\t2
use e:\mbg\10-12月消费大于360元用户清单
close all
use e:\mbg\10-12月消费大于360元用户清单
close all
use e:\mbg\t1
append from e:\mbg\t2
close all
use e:\mbg\商务客户资料.dbf
modify struc
close all
sele a.*,b.客户经理 as 商务经理 from e:\mbg\t1 a,e:\mbg\商务客户资料.dbf b where a.手机号码=b.手机号码
copy to e:\mbg\t3
sele * from e:\mbg\t1 wher 手机号码 not in (sele 手机号码 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.是否电子钱,b.到期日 from e:\mbg\t3 a,e:\mbg\11月电子钱包清单 b where a.手机号码=b.手机号码
copy to e:\mbg\t5
sele * from e:\mbg\t3 wher 手机号码 not in (sele 手机号码 from e:\mbg\t5)
copy to e:\mbg\t6
close all
use e:\mbg\t5
append from e:\mbg\t6
close all
sele a.*,b.受理时间 from e:\mbg\t5 a,e:\mbg\电子钱包收费明细表11-12月.DBF b where a.手机号码=b.手机号码
copy to e:\mbg\t7
sele * from e:\mbg\t5 wher 手机号码 not in (sele 手机号码 from e:\mbg\t7)
copy to e:\mbg\t8
close all
use e:\mbg\t7
append from e:\mbg\t8
close all
sele * from e:\mbg\t5
close all
sele a.*,b.受理时间 from e:\mbg\t5 a,e:\mbg\电子钱包收费明细表11-12月.DBF b where a.手机号码=b.手机号码
sele a.*,b.受理时间 from e:\mbg\t5 a,e:\mbg\电子钱包收费明细表11-12月.DBF b where a.手机号码=b.手机号码 group by 手机号码
sele a.*,b.受理时间 from e:\mbg\t5 a,e:\mbg\电子钱包收费明细表11-12月.DBF b where a.手机号码=b.手机号码
copy to e:\mbg\t7
sele * from e:\mbg\t7 group by 手机号码
copy to e:\mbg\t8
sele * from e:\mbg\t5 wher 手机号码 not in (sele 手机号码 from e:\mbg\t8)
copy to e:\mbg\t9
close all
use e:\mbg\t8
append from e:\mbg\t9
close all
sele * from e:\mbg\t8
close all
sele * from e:\mbg\品牌参数表.dbf
sele * from e:\mbg\t8
close all
sele * from e:\mbg\品牌参数表.dbf
sele a.*,b.品牌名称 from e:\mbg\t8 a,e:\mbg\品牌参数表.dbf b where a.brand_code=b.品牌编码
sele a.*,b.品牌名称 from e:\mbg\t8 a,e:\mbg\品牌参数表.dbf b where a.品牌=b.品牌编码
copy to e:\mbg\拍照中高端明细信息
copy to e:\mbg\拍照中高端明细信息 type xls
copy to e:\mbg\拍照中高端明细信息1 type xls for recno()>16383
sele * from e:\mbg\t5 where develop_de not in (sele develop_de from e:\mbg\计算城区公话的参数表.dbf where 是否农村='农村') into cursor s1