导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿
会员中心
收藏
消息
创作中心

高分求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+

社区成员

25.4w+

社区内容

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