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

SQL数量统计问题,请教
TYPE 状态
40A 好
40B 坏
40B 好
40C 好
40A 好



现在要显示成
  40A 40B 40C . 。。。。。。
好 2 1 1
坏 0 1 0
ToTal 2 2 1



注意:TYPE中类型很多,只列了3种,如何横向列出type

------解决方案--------------------
SQL code
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([TYPE] varchar(10),[状态] varchar(10))
insert [tb]
select '40A','好' union all
select '40B','坏' union all
select '40B','好' union all
select '40C','好' union all
select '40A','好'


declare @s varchar(max)
set @s=''
select @s=@s+','+quotename([TYPE])+'=sum(case when [TYPE]='+quotename([TYPE],'''')+' then 1 else 0 end)'
from [tb] group by[TYPE]
set @s = 'select isnull([状态],''ToTal'')'+@s+'  from [tb] group by [状态] with ROLLUP'
exec(@s)
/*
           40A         40B         40C
---------- ----------- ----------- -----------
好          2           1           1
坏          0           1           0
ToTal      2           2           1

*/

------解决方案--------------------
生成的語句格式如下:
SQL code
select 
    [状态]=isnull([状态],'ToTal'),
    [40A]=sum(case when [TYPE]='40A' then 1 else 0 end),
    [40B]=sum(case when [TYPE]='40B' then 1 else 0 end),
    [40C]=sum(case when [TYPE]='40C' then 1 else 0 end)
from #T group by [状态] with rollup;

------解决方案--------------------
SQL code

create table [tb]([TYPE] varchar(10),[状态] varchar(10))

insert [tb]
select '40A','好' union all
select '40B','好' union all
select '40B','好' union all
select '40C','好' union all
select '40A','好'


declare @s varchar(max)=''

select @s=@s+','+quotename([TYPE])+'=sum(case when [TYPE]='+quotename([TYPE],'''')+' then 1 else 0 end)'
from [tb] group by[TYPE]

set @s = 'select isnull([状态],''ToTal'')'+@s+' from (select * from [tb] union all select ''x'',''坏'' union all select ''x'',''好'') t group by [状态] with ROLLUP'

exec(@s)

           40A         40B         40C
---------- ----------- ----------- -----------
好          2           2           1
坏          0           0           0
ToTal      2           2           1

(3 row(s) affected)