sql语句统计数量，统计一个字段的值的数量

pivot

--SQL SERVER 2000 静态SQLselect type as type ,
max(case level when '一级' then id else 0 end) 一级,
max(case level when '二级' then id else 0 end) 二级,
max(case level when '三级' then id else 0 end) 三级,
count(id) 总数量
from tb
group by type
然后再汇总，或者可以写个视图就ok了

--SQL SERVER 2000 动态SQL
declare @sql varchar(8000)
set @sql = 'select type '
select @sql = @sql + ' , max(case level when ''' + level + ''' then id else 0 end) [' + level + ']'
from (select distinct level from tb) as a
set @sql = @sql + ' from tb group by type'
exec(@sql)

--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(id) for level in (一级,二级,三级)) b

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + level from tb group by level
exec ('select * from (select * from tb) a pivot (max(id) for level in (' + @sql + ')) b')

select type,count(*) as 总数量,
sum(case when level='一级' then 1 else 0 end) as 一级,
sum(case when level='二级' then 1 else 0 end) as 二级,
sum(case when level='三级' then 1 else 0 end) as 三级
from table group by type

楼上的应该改改吧

select type,sum(*) as 总数量,
sum(case when level='一级' then 1 else 0 end) as 一级,
sum(case when level='二级' then 1 else 0 end) as 二级,
sum(case when level='三级' then 1 else 0 end) as 三级
from table group by type

select type,count(1) from table group by type
union all
select level,count(1) from table group by level