高分求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最有效如何写？

...全文

111 22 打赏收藏转发到动态举报

写回复

用AI写文章

22 条回复

切换为时间正序

请发表友善的回复…

发表回复

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