sql统计查询

--手机短信记录表
create table SmsLogs(
Id int primary key identity(1,1),--自增id
Mobile nvarchar(20) not null,--手机号码
SmsContent nvarchar(255),--短信内容
MobileCity nvarchar(20),--手机号归属地城市
SendDate datetime not null,--短消息发送时间
CardNum nvarchar(50),--发送的卡号
CardNumCity nvarchar(20),--卡号使用的城市
Prize int--所中奖项 0:为未中奖;1:一等奖;2:为二等奖. 以此类推
);

insert dbo.SmsLogs values('555555','您好','广州','2012-04-17 16:51:27.077','4','上海',0)
insert dbo.SmsLogs values('666666','您好','广州','2012-04-17 16:51:27.077','5','北京',2)
insert dbo.SmsLogs values('777777','您好','上海','2012-04-17 16:51:27.077','7','上海',0)
insert dbo.SmsLogs values('999999','您好','广州','2012-04-17 16:51:27.077','8','北京',3)

insert dbo.SmsLogs values('234567','您好','北京','2012-04-17 16:51:27.077','6','广州',1)
insert dbo.SmsLogs values('454545','您好','北京','2012-04-17 16:51:27.077','9','广州',1)

 

if(exists(select name from tempdb..sysobjects where name like'%temp%' and type='U')) drop table #temp
select CardNumCity 城市,
sum(b.一等奖)+sum(b.二等奖)+ sum(b.三等奖)+SUM (b.没等奖) 参与人数,
sum(b.一等奖) 一等奖,
sum(b.二等奖) 二等奖,
sum(b.三等奖)三等奖
into #temp
from (
select a.CardNumCity,
sum( case a.Prize when '0' then 1 when '2' then 0 when '3' then 0  else 0 end) 没等奖,
sum( case a.Prize when '1' then 1 when '2' then 0 when '3' then 0  else 0 end) 一等奖,
sum( case a.Prize when '2' then 1 when '1' then 0 when '3' then 0  else 0 end) 二等奖,
sum( case a.Prize when '3' then 1 when '2' then 0 when '1' then 0  else 0 end) 三等奖
from
(select CardNumCity,Prize from SmsLogs) a group by a.CardNumCity,a.Prize)b group by b.CardNumCity

select * from  #temp
union all
select  城市 = '总计' , 参与人数 = sum(参与人数) , 一等奖 = sum(一等奖) ,二等奖 = sum(二等奖),三等奖 = sum(三等奖) from #temp