select 学号,数量 = case when col1 > 某分数 then 1 else 0 end + case when col2 > 某分数 then 1 else 0 end + ... case when coln > 某分数 then 1 else 0 end from tb
select * from ( select stu_Id,sum(score) score from tab group by stu_id ) a where a.score>=@score
谢谢,ojuju10帮助,不过我是需要统计每列都大于同一值的列数,呵呵,不是算总成绩,谢谢
create table tb(name varchar(10),a int,b int) insert into tb select '王',60,70 insert into tb select '李',90,95declare @sql varchar(8000) select @sql=isnull(@sql+'+','')+'sum(case when '+name+'>60 then 1 else 0 end)' from (select * from syscolumns where id=object_id('tb') and name<>'name')tp exec('select name,'+@sql+' from tb group by name') 李 2 王 1
select 学号,数量 =
case when col1 > 某分数 then 1 else 0 end +
case when col2 > 某分数 then 1 else 0 end +
...
case when coln > 某分数 then 1 else 0 end
from tb
select stu_Id,sum(score) score
from tab
group by stu_id
) a
where a.score>=@score
insert into tb select '王',60,70
insert into tb select '李',90,95declare @sql varchar(8000)
select @sql=isnull(@sql+'+','')+'sum(case when '+name+'>60 then 1 else 0 end)'
from (select * from syscolumns where id=object_id('tb') and name<>'name')tp
exec('select name,'+@sql+' from tb group by name')
李 2
王 1