tracfone developed sql

以下是大数据量（千万条）处理涉及到的相关SQL
-- 用in进行匹配，删除CAT_Billing_Blocked表中与DEACT_PHONENUMBER表相同的数据
delete from Beta_core_Billing_Beta8.dbo.CAT_Billing_Blocked
where PhoneNumber in (
select '1' +  PhoneNumber from Beta_Core_Psmsg_Beta8.dbo.DEACT_PHONENUMBER);
-- 用=进行匹配，删除CAT_Billing_Blocked表中与DEACT_PHONENUMBER表相同的数据
delete a from Beta_core_Billing_Beta8.dbo.CAT_Billing_Blocked a,Beta_Core_Psmsg_Beta8.dbo.DEACT_PHONENUMBER b
where substring(a.PhoneNumber,2,len(a.PhoneNumber)) = b.PhoneNumber;
--插入一行数据到CAT_Billing_Blocked表
insert into Beta_core_Billing_Beta8.dbo.CAT_Billing_Blocked(
UserId,PhoneNumber,BlockedDate,BlockedReason,AccGroupId,CarrierId,ShortCode,Amount
) values(
333,'1'+ '5416541634','2008-01-21','test',1,23,77888,600
)
--查询CAT_Billing_Blocked表的所有数据
select * from Beta_core_Billing_Beta8.dbo.CAT_Billing_Blocked;
--删除CAT_Billing_Blocked表的所有数据
delete from Beta_core_Billing_Beta8.dbo.CAT_Billing_Blocked;
--------------------------------------------------------------------------
--插入一行数据到DEACT_PHONENUMBER表
insert into Beta_Core_Psmsg_Beta8.dbo.DEACT_PHONENUMBER(PhoneNumber) values('14236533152');
--查询DEACT_PHONENUMBER表所有数据行数
select count(*) from Beta_Core_Psmsg_Beta8.dbo.DEACT_PHONENUMBER;
--删除DEACT_PHONENUMBER所有数据
delete from Beta_Core_Psmsg_Beta8.dbo.DEACT_PHONENUMBER;
--求PhoneNumber长度
select len(PhoneNumber) as PhoneNumberLength from Beta_Core_Psmsg_Beta8.dbo.DEACT_PHONENUMBER;
--执行存储过程，从网络上一台机器读取文件（含大量数据）
exec Beta_Core_Psmsg_Beta8.dbo.ps_Deact_Phonenumber_Import '\\172.10.10.11\tracfone\phone.txt';
------------------------------------------------------------------------------------------
--删除日志
dump tran Beta_core_Billing_Beta8 with no_log;
dump tran Beta_Core_Psmsg_Beta8 with no_log;
--插入500000条数据到CAT_Billing_Blocked从DEACT_PHONENUMBER
insert into Beta_core_Billing_Beta8.dbo.CAT_Billing_Blocked(
UserId,PhoneNumber,BlockedDate,BlockedReason,AccGroupId,CarrierId,ShortCode,Amount
) select top 500000 22,'1'+PhoneNumber,'2008-1-1','tracfone',1,2,44888,888 from Beta_Core_Psmsg_Beta8.dbo.DEACT_PHONENUMBER;
--求CAT_Billing_Blocked的行数
select count(*) from Beta_core_Billing_Beta8.dbo.CAT_Billing_Blocked;
--求PhoneNumber子串，从第2位开始到结尾
select substring(PhoneNumber,2,len(PhoneNumber)) from Beta_core_Billing_Beta8.dbo.CAT_Billing_Blocked;

评论

2 楼 sailinglxg 2008-05-05  

由于工作忙，上面的SQL没有加中文详细说明，很不好意思。
现在我加些中文说明，有时间可以多交流。

1 楼 chenshuangjiang 2008-04-30  

我是一个新手,一直在学SQL。请问您这段SQL是做什么用的，我看着有点晕。能说明一下吗。谢谢了。我看了您大多的原创文章都不错。向您学习