短信按运营商统计的实现

欲做这样的查询：

 

 

表结构如下，仅用这个表来存储短信，无运营商的字段：

/*==============================================================*/ /* Table: tSMS_His */ /*==============================================================*/ create table tSMS_His ( FromID varchar(10) not null, PhoneID char(11) not null, createDate datetime not null, Content1 varchar(200) not null, Content2 varchar(200) not null, SendDate datetime not null ) go

 

 

最初雏形，无法完成按月统计，仅能统计各运营商的发送总量

--联通 select COUNT(1) as '中国联通' from tsms_his WHERE (PhoneID LIKE '130%' or PhoneID LIKE '131%' or PhoneID LIKE '132%' or PhoneID LIKE '155%' or PhoneID LIKE '156%' or PhoneID LIKE '157%' or PhoneID LIKE '186%') AND (senddate > '2010-1-1' AND senddate < '2010-5-22' ) AND fromid='ccstest001' --电信 select COUNT(1) as '中国电信' from tsms_his WHERE (PhoneID LIKE '133%' or PhoneID LIKE '153%' or PhoneID LIKE '189%' or PhoneID LIKE '0%') AND (senddate > '2010-1-1' AND senddate < '2010-5-22' ) AND fromid='ccstest001' --移动 select COUNT(1) AS '中国移动' from tsms_his WHERE (PhoneID LIKE '134%' OR PhoneID LIKE '135%' OR PhoneID LIKE '136%' OR PhoneID LIKE '137%' OR PhoneID LIKE '138%' OR PhoneID LIKE '139%' or PhoneID LIKE '150%' or PhoneID LIKE '151%' or PhoneID LIKE '152%' or PhoneID LIKE '154%' or PhoneID LIKE '157%' or PhoneID LIKE '158%' or PhoneID LIKE '159%' or PhoneID LIKE '187%' or PhoneID LIKE '188%') AND (senddate > '2010-1-1' AND senddate < '2010-5-22' ) AND fromid='ccstest001'

 

 

询问高手，修改之,case-when-then很强大:

select convert(varchar(7),SendDate,120) as '月份', case when (PhoneID LIKE '130%' or PhoneID LIKE '131%' or PhoneID LIKE '132%' or PhoneID LIKE '155%' or PhoneID LIKE '156%' or PhoneID LIKE '157%' or PhoneID LIKE '186%') then '中国联通' when (PhoneID LIKE '133%' or PhoneID LIKE '153%' or PhoneID LIKE '189%' or PhoneID LIKE '0%') then '中国电信' when (PhoneID LIKE '134%' OR PhoneID LIKE '135%' OR PhoneID LIKE '136%' OR PhoneID LIKE '137%' OR PhoneID LIKE '138%' OR PhoneID LIKE '139%' or PhoneID LIKE '150%' or PhoneID LIKE '151%' or PhoneID LIKE '152%' or PhoneID LIKE '154%' or PhoneID LIKE '157%' or PhoneID LIKE '158%' or PhoneID LIKE '159%' or PhoneID LIKE '187%' or PhoneID LIKE '188%') then '中国移动' end as '运营商', count(*) as '发送量' from tSMS_His where (senddate > '2010-1-1' AND senddate < '2010-5-22' ) AND fromid='ccstest001' group by case when (PhoneID LIKE '130%' or PhoneID LIKE '131%' or PhoneID LIKE '132%' or PhoneID LIKE '155%' or PhoneID LIKE '156%' or PhoneID LIKE '157%' or PhoneID LIKE '186%') then '中国联通' when (PhoneID LIKE '133%' or PhoneID LIKE '153%' or PhoneID LIKE '189%' or PhoneID LIKE '0%') then '中国电信' when (PhoneID LIKE '134%' OR PhoneID LIKE '135%' OR PhoneID LIKE '136%' OR PhoneID LIKE '137%' OR PhoneID LIKE '138%' OR PhoneID LIKE '139%' or PhoneID LIKE '150%' or PhoneID LIKE '151%' or PhoneID LIKE '152%' or PhoneID LIKE '154%' or PhoneID LIKE '157%' or PhoneID LIKE '158%' or PhoneID LIKE '159%' or PhoneID LIKE '187%' or PhoneID LIKE '188%') then '中国移动' end, convert(varchar(7),SendDate,120)

 

 

这样的话，结果出来了，代码量还是不小……改之：

select convert(varchar(7),SendDate,120) as '月份', CASE WHEN CONVERT(VARCHAR(3),PhoneID) IN ('130','131','132','155','156','157','186') THEN '中国联通' WHEN CONVERT(VARCHAR(3),PhoneID) IN ('134','135','136','137','138','139','150','151','152','154','159','187','188') THEN '中国移动' ELSE '中国电信' end as '通信运营商',count(*) as '发送量' from tSMS_His GROUP BY CASE WHEN CONVERT(VARCHAR(3),PhoneID) IN ('130','131','132','155','156','157','186') THEN '中国联通' WHEN CONVERT(VARCHAR(3),PhoneID) IN ('134','135','136','137','138','139','150','151','152','154','159','187','188') THEN '中国移动' ELSE '中国电信' end,convert(varchar(7),SendDate,120)