建表：CREATE TABLE [dbo].[hao070916] (
[hao] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[content] [varchar] (140) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
乱序:select hao into #t from hao070916 order by newid()
drop table hao070916
select * into hao070916 from #t
总发送数：select count(*) from hao070916
过滤后号码：select count(distinct hao) from hao070916
最长号码：select max(len(hao)) from hao070916
最短号码：select min(len(hao)) from hao070916
是否有全角：select hao from hao070916 where hao like'%０%' --在字段里选上区分大小和区分宽度
显示一定长度的内容：select content from hao070916 where len(content)=20
查找空内容：select content from hao070916 where content is null
监测还有多少条没发：select count(*) from dyhikemessages where send_out_flag=0
插入数据：insert into dyhikemessages(to_mobile)
select col001
from wu.dbo.gm080118
where seq>=400000 order by newid()
select distinct mobi into lby from data1 where city like'%深圳%' and mobi in (select mobi from qqt where addr like'%宝安%')
备份发送的表：select * into dyhikemessages_gm080118_bak from dyhikemessages
删除初始表：truncate table dyhikemessages
筛选号码段:select * from [071229_1] where left(col001,3) in('130','131','132','133','153','156')

---------------------------------------------------------------------------------------------------------以下是代发时常用的模板
--发监测
insert into sms_server.dbo.dyhikemessages(to_mobile,msg_content,prefix,epid)
select '13632574155',
'<内容,内容,>',
'','<EPID,epid,>'
--选出数据
select top 19997 <col001,列名,col001>,id from <wu.dbo.华侨城车主6W_0125,数据源表名,>
where 发送='未发'
order by id
--插入数据
insert into sms_server.dbo.dyhikemessages(to_mobile)
select top 19997 <col001,列名,col001> from <wu.dbo.华侨城车主6W_0125,数据源表名,>
where 发送='未发'
order by id
--插入内容
update sms_server.dbo.dyhikemessages
set msg_content='<内容,内容,>'
------把未发标为已发
update <wu.dbo.华侨城车主6W_0125,数据源表名,> set 发送='<已发0128,发送日期,>' where <col001,列名,col001> in (
select top 19997 <col001,列名,col001> from <wu.dbo.华侨城车主6W_0125,数据源表名,>
where 发送='未发'
order by id
)
--插入epid
update sms_server.dbo.dyhikemessages
set epid='<EPID,epid,>'
--监视
select count(*) from sms_server.dbo.dyhikemessages where send_out_flag=0
--备份
select * into send_yifa.dbo.dyhikemessages_<gm080118,备份的表名,>_bak from sms_server.dbo.dyhikemessages
--如始化
truncate table sms_server.dbo.dyhikemessages
-----------------------------------
--过滤号
----去空格
update <表,表,> set <col001,列名,col001> =replace(<col001,列名,col001>,' ','')
----处理移动的号码,剩下的就是有误的或是小灵通的了
select distinct <col001,列名,col001> from <表,表,> where <col001,列名,col001> like '[1][3,5][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
-------选出小灵通的
select distinct <col001,列名,col001> from <表,表,>
where substring(<col001,列名,col001>,1,1) in ('0')
and substring(<col001,列名,col001>,2,1) not in ('0')