日期:2014-05-18  浏览次数:20376 次

一个分组统计的问题
select   distinct   housetype_name,count(housing_id)   as   housingNum,(select   count(usestatus_name)   from   v_housing   where   usestatus_no   != '3 '   and   usekind_no   = '17 ')   as   houseusefull,(select   count(usestatus_name)   from   v_housing   where   usestatus_no   =   '1 '   and   usekind_no   = '17 ')   as   houseused,(select   count(usestatus_name)   from   v_housing   where   usestatus_no   =   '2 '   and   usekind_no   = '17 ')   as   housefull,(select   count(usestatus_name)   from   v_housing   where   usestatus_no   =   '3 '   and   usekind_no   = '17 ')   as   houseother   from   v_housing   where   usekind_no   = '17 '   group   by   housetype_name      

现在我要求子查询也按照housetype_name来进行分组,各位帮忙看看!

------解决方案--------------------
select distinct housetype_name,count(housing_id) as housingNum,
sum(case when usestatus_no != '3 ' and usekind_no = '17 ' then 1 else 0 end) as houseusefull,
sum(case when usestatus_no = '1 ' and usekind_no = '17 ' then 1 else 0 end) as houseused,
sum(case when usestatus_no = '2 ' and usekind_no = '17 ' then 1 else 0 end) as housefull,
sum(case when usestatus_no = '3 ' and usekind_no = '17 ' then 1 else 0 end) as houseother
from v_housing where usekind_no = '17 ' group by housetype_name