当前位置: 代码迷 >> Sql Server >> 求一SQL语句-》。马上揭贴
  详细解决方案

求一SQL语句-》。马上揭贴

热度:83   发布时间:2016-04-27 15:13:59.0
求一SQL语句-----》在线等。马上揭贴`
有一张表
t_folder

case_id   case_name   status
1                           a           1         //已办
2                           b           2         //未办理
3                           c           3         //转办
4                           d           1
5                           e           3

要求的结果     统计:如下
总数       已办       未办理       转办
5               2                 1               2

我用select   count(*)   as   总数,(select   count(*)   from   t_folder   where   status=1)   as   已办   from   t_folder
这样不行``用inner   join也没做好```

------解决方案--------------------
select count(*) as 总数,sum(case status when '1 ' then 1 else 0 end) as 已办 from t_folder
------解决方案--------------------
create table t_folder(case_id int,case_name char(1),status int)
insert into t_folder
select 1, 'a ',1
union all select 2, 'b ',2
union all select 3, 'c ',3
union all select 4, 'd ',1
union all select 5, 'e ',3

select count(*) as '总数 ',
sum(case when status = 1 then 1 else 0 end) as '已办 ',
sum(case when status = 2 then 1 else 0 end) as '未办理 ',
sum(case when status = 3 then 1 else 0 end) as '转办 '
from t_folder

/*
总数 已办 未办理 转办
----------- ----------- ----------- -----------
5 2 1 2

(所影响的行数为 1 行)
*/
------解决方案--------------------
楼主的速度真快
------解决方案--------------------
select count(case_id),
已办=sum(case when status=1 then 1 else 0 end),
未办理=sum(case when status=2 then 1 else 0 end),
转办=sum(case when status=3 then 1 else 0 end)
from t_folder
------解决方案--------------------
select count(*) as 总数,sum(case status when '1 ' then 1 else 0 end) as 已办,
sum(case status when '2 ' then 1 else 0 end) as 未办理,
sum(case status when '3 ' then 1 else 0 end) as 转办 from t_folder

------解决方案--------------------
select (select count(*) from t_folder) as 总数,(select count(*) from t_folder where status=1) as 已办,(select count(*) from t_folder where status=2) as 未办理,(select count(*) from t_folder where status=3) as 转办
  相关解决方案