导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

高分求SQL统计

CheerYang 2007-12-18 09:39:49
-----------------------------------------------
天数 | 未回复 | 正在处理中 | 已回复 | 无效 |
-----------------------------------------------
0-7 | 1 | 2 | 3 | 4 |
-----------------------------------------------
7-15 | 11 | 12 | 13 | 14 |
-----------------------------------------------
15-30| 5 | 3 | 0 | 0 |
-----------------------------------------------
>30 | 4 | 5 | 6 | 0 |
-----------------------------------------------
累计 | 21 | 22 | 22 | 18 |
-----------------------------------------------

我想统计上面的这张报表的数据.其中假设表名为tablename,只用到其中2个字段
一个是input_time (datetime型/存放录入日期) ;
另一个是flag (char(1)型/存放标志):flag=0--未处理 ; flag=1--正在处理中 ; flag=2--已回复 ; flag=3--无效

我想第一行统计input_time与当前日期比较在0-7天(注意datetime型含有时间,时间上比较够24小时也算1天)分别未回复/已回复 等数据
第2、3、4行类似。最后一行累计。

这样的SQL最有效如何写?
...全文
80 点赞 收藏 22
写回复
22 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
CheerYang 2007-12-18
select isnull(天数,'累计') 天数 ,
sum(case flag when 0 then 1 else 0 end) '未处理',
sum(case flag when 1 then 1 else 0 end) '正在处理中',
sum(case flag when 2 then 1 else 0 end) '已回复',
sum(case isnull(flag,3) when 3 then 1 else 0 end) '无效'
from
(
select input_time , flag , 天数 =
case
when datediff(day,input_time,getdate()) between 0 and 7 then '0-7'
when datediff(day,input_time,getdate()) between 8 and 15 then '8-15'
when datediff(day,input_time,getdate()) between 16 and 30 then '16-30'
when datediff(day,input_time,getdate()) > 30 then '>30'
end
from letter_files
) t
group by 天数 with rollup

----
累计有二行,重复了.
回复
-狙击手- 2007-12-18
union 改成union all 会好点
回复
-狙击手- 2007-12-18
无枪狙击手:累计一行是正确的,SQL精简.
潇洒老乌龟:第一行有多余NULL
背着灵魂漫步:累计一行不正确.

试问union 与子查询哪个效率高.

问完这个马上结贴.

----
从查询计划来看,子查询高
回复
dawugui 2007-12-18
无枪狙击手:累计一行是正确的,SQL精简.
潇洒老乌龟:第一行有多余NULL
背着灵魂漫步:累计一行不正确.

试问union 与子查询哪个效率高.

问完这个马上结贴.

已经修改,空改为合计.
回复
-狙击手- 2007-12-18
select isnull(天数,'累计') 天数 ,
sum(case flag when 0 then 1 else 0 end) '未处理',
sum(case flag when 1 then 1 else 0 end) '正在处理中',
sum(case flag when 2 then 1 else 0 end) '已回复',
sum(case isnull(flag,3) when 3 then 1 else 0 end) '无效'
from
(
select input_time , flag , 天数 =
case
when datediff(day,input_time,getdate()) between 0 and 7 then '0-7'
when datediff(day,input_time,getdate()) between 8 and 15 then '8-15'
when datediff(day,input_time,getdate()) between 16 and 30 then '16-30'
when datediff(day,input_time,getdate()) > 30 then '>30'
end
from tablename
) t
group by 天数 with rollup
回复
CheerYang 2007-12-18
无枪狙击手:累计一行是正确的,SQL精简.
潇洒老乌龟:第一行有多余NULL
背着灵魂漫步:累计一行不正确.

试问union 与子查询哪个效率高.

问完这个马上结贴.
回复
dawugui 2007-12-18
select isnull(天数,'合计') 天数 ,
sum(case flag when 0 then 1 else 0 end) '未处理',
sum(case flag when 1 then 1 else 0 end) '正在处理中',
sum(case flag when 2 then 1 else 0 end) '已回复',
sum(case flag when 3 then 1 else 0 end) '无效'
from
(
select input_time , flag , 天数 =
case
when datediff(day,input_time,getdate()) between 0 and 7 then '0-7'
when datediff(day,input_time,getdate()) between 8 and 15 then '8-15'
when datediff(day,input_time,getdate()) between 16 and 30 then '16-30'
when datediff(day,input_time,getdate()) > 30 then '>30'
end
from tablename
) t
group by 天数
with rollup
回复
-狙击手- 2007-12-18
select isnull(天数,'累计') 天数 ,
sum(case flag when 0 then 1 else 0 end) '未处理',
sum(case flag when 1 then 1 else 0 end) '正在处理中',
sum(case flag when 2 then 1 else 0 end) '已回复',
sum(case flag when 3 then 1 else 0 end) '无效'
from
(
select input_time , flag , 天数 =
case
when datediff(day,input_time,getdate()) between 0 and 7 then '0-7'
when datediff(day,input_time,getdate()) between 8 and 15 then '8-15'
when datediff(day,input_time,getdate()) between 16 and 30 then '16-30'
when datediff(day,input_time,getdate()) > 30 then '>30'
end
from tablename
) t
group by 天数 with rollup
回复
dawugui 2007-12-18
select 天数 ,
sum(case flag when 0 then 1 else 0 end) '未处理',
sum(case flag when 1 then 1 else 0 end) '正在处理中',
sum(case flag when 2 then 1 else 0 end) '已回复',
sum(case flag when 3 then 1 else 0 end) '无效'
from
(
select input_time , flag , 天数 =
case
when datediff(day,input_time,getdate()) between 0 and 7 then '0-7'
when datediff(day,input_time,getdate()) between 8 and 15 then '8-15'
when datediff(day,input_time,getdate()) between 16 and 30 then '16-30'
when datediff(day,input_time,getdate()) > 30 then '>30'
end
from tablename
) t
group by 天数

回复
-狙击手- 2007-12-18
'无效'=sum(case isnull(flag,3) when 3 then 1 else 0 end)
回复
中国风 2007-12-18
要换一下列的位:
sum(case when flag = 3 or flag is null then 1 else 0 end)
回复
CheerYang 2007-12-18
累计一行中的无效一列总数不对呀.
回复
-狙击手- 2007-12-18
create table letter_files(input_time datetime,flag int)
go
select '0-7' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from letter_files
where datediff(dd,input_time,getdate()) between 0 and 7
union
select '7-15' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from letter_files
where datediff(dd,input_time,getdate()) between 7 and 15
union
select '15-30' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from letter_files
where datediff(dd,input_time,getdate()) between 15 and 30
union
select '大于30' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from letter_files
where datediff(dd,input_time,getdate()) > 30
union
select '累计' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from letter_files
order by 天数

drop table letter_files
回复
CheerYang 2007-12-18
顺便问下,如果flag=3或flag is null 均为"无效"

是否这样写?

'无效'=sum(case flag when 3 or is null then 1 else 0 end)
回复
CheerYang 2007-12-18
select '0-7' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from letter_files
where datediff(dd,input_time,getdate()) between 0 and 7
union
select '7-15' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from letter_files
where datediff(dd,input_time,getdate()) between 7 and 15
union
select '15-30' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from letter_files
where datediff(dd,input_time,getdate()) between 15 and 30
union
select '>30' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from letter_files
where datediff(dd,input_time,getdate()) >30
union
select '累计' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from letter_files


背着灵魂漫步等同志累了.但结果如下:


>30 0 0 0 1
0-7 0 0 0 0
15-30 3 0 1 0
7-15 0 0 0 0
累计 3 0 1 3

如何排序呀?(0-7第一行,7-15第二行...)
回复
pt1314917 2007-12-18
ss为表名。。忘记换了`
回复
-狙击手- 2007-12-18
楼上辛苦 了
回复
pt1314917 2007-12-18
对了。把union换成union all。这样效率会高一点的。 。刚才手快。没有换。。
回复
pt1314917 2007-12-18



select '0-7' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from ss
where datediff(dd,input_time,getdate()) between 0 and 7
union
select '7-15' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from ss
where datediff(dd,input_time,getdate()) between 7 and 15
union
select '15-30' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from ss
where datediff(dd,input_time,getdate()) between 15 and 30
union
select '>30' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from ss
where datediff(dd,input_time,getdate()) >30
union
select '累计' '天数',
'未回复'=sum(case flag when 0 then 1 else 0 end),
'正在处理中'=sum(case flag when 1 then 1 else 0 end),
'已回复'=sum(case flag when 2 then 1 else 0 end),
'无效'=sum(case flag when 3 then 1 else 0 end)
from ss

回复
-狙击手- 2007-12-18
如楼上进行分组后,最后在group by 后加上with rollup
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告