selct id
,count(*) 总条数
,sum(case when col1=0 then 1 else 0 end) COL1值为0的记录数
,sum(case when col1=1 then 1 else 0 end) COL1值为1的记录数
,sum(case when col1=2 then 1 else 0 end) COL1值为2的记录数
from 表 group by id
,count(*) 总条数
,sum(case when col1=0 then 1 else 0 end) COL1值为0的记录数
,sum(case when col1=1 then 1 else 0 end) COL1值为1的记录数
,sum(case when col1=2 then 1 else 0 end) COL1值为2的记录数
from 表 group by id
,count(*) 总条数
,sum(case when col1=0 then 1 else 0 end) COL1值为0的记录数
,sum(case when col1=1 then 1 else 0 end) COL1值为1的记录数
,sum(case when col1=2 then 1 else 0 end) COL1值为2的记录数
from 表 group by id
http://expert.csdn.net/Expert/topic/1986/1986327.xml?temp=.1703302
http://expert.csdn.net/Expert/topic/1986/1986537.xml?temp=.9920465谢谢
,count(*) as 总条数
,sum(case when col1=0 then 1 else 0 end) COL1值为0的记录数
,sum(case when col1=1 then 1 else 0 end) COL1值为1的记录数
,sum(case when col1=2 then 1 else 0 end) COL1值为2的记录数
from 表 group by id order by 总条数
,sum(case col1 when 0 then 1 else 0 end) as col1为0的条数
,sum(case col1 when 1 then 1 else 0 end) as col1为1的条数
,sum(case col1 when 2 then 1 else 0 end) as col1为2的条数
from 表
group by id
declare @sql varchar(8000)
set @sql = 'select ID,count(*) 总条数'
select @sql = @sql + ',sum(case COL1 when '+cast(COL1 as varchar(10))+' then 1 end) [COL1值为'+cast(COL1 as varchar(10))+'的记录数]'
from (select distinct COL1 from 表) as a
select @sql = @sql+' from 表 group by ID'exec(@sql)
go
set @sql = 'select ID,count(*) 总条数'
select @sql = @sql + ',sum(case COL1 when '+cast(COL1 as varchar(10))+' then 1 end) [COL1值为'+cast(COL1 as varchar(10))+'的记录数]'
from (select distinct COL1 from 表 order by COL1 desc) as a
select @sql = @sql+' from 表 group by ID'exec(@sql)
go
declare @sql varchar(8000)
set @sql = 'select ID,count(*) 总条数'
select @sql = @sql + ',sum(case COL1 when '+cast(COL1 as varchar(10))+' then 1 end) [COL1值为'+cast(COL1 as varchar(10))+'的记录数]'
from (select distinct top 10000000 COL1 from 表 order by COL1 desc) as a
select @sql = @sql+' from 表 group by ID'exec(@sql)
go