select sum(case 分数 when 65 then 1 else 0 end) 65,
sum(case 分数 when 70 then 1 else 0 end) 70,
sum(case 分数 when 90 then 1 else 0 end) 90
from 成绩表 group by 分数
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + ' sum(case 分数 when '''+分数+''' then 1 end) ['+分数+']'
from (select distinct 分数 from 成绩表) as a
select @sql = @sql+' from 成绩表 '
exec(@sql)
select
case
when 分数<50 then [50以下]
when 分数>50 and 分数<60 then [50-60分]
....
end
,count(*) from 成绩表
group by
case
when 分数<50 then [50以下]
when 分数>50 and 分数<60 then [50-60分]
....
end
应该是分数段比较实用.
select (select count(分数) from tablename where 分数=65) [65],(select count(分数) from tablename where 分数=70) [70],(select count(分数) from tablename where 分数=90) [90]
select sum(case 分数 when 65 then 1 else 0 end) as 65,
sum(case 分数 when 70 then 1 else 0 end) as 70,
sum(case 分数 when 90 then 1 else 0 end) as 90
from 表 group by 分数