(1):
select '100分以上个数' as 分组,
科目成绩1 = sum( case when 科目成绩1>100 then 1 else 0 end),
科目成绩2 = sum( case when 科目成绩2>100 then 1 else 0 end),
......
from tablename
union all
select '30分以上个数',
科目成绩1 = sum( case when 科目成绩1>30 then 1 else 0 end),
科目成绩2 = sum( case when 科目成绩2>30 then 1 else 0 end),
......
from tablename(2):
select '100分以上个数' as 分组,
科目成绩1 = sum( case when 科目成绩1>100 then 1 else 0 end),
科目成绩2 = sum( case when 科目成绩2>100 then 1 else 0 end),
......
from tablename
union all
select '30分以上个数',
科目成绩1 = sum( case when 科目成绩1>30 then 1 else 0 end),
科目成绩2 = sum( case when 科目成绩2>30 then 1 else 0 end),
......
from tablename(2):
(3)select class, avg(score) as 平均分 from tbNAME a where (select count(1) from tbname where class=a.class and score>a.score)<5 group by class
select '100分以上个数' as 分组,
科目成绩1 = sum( case when 科目成绩1>100 then 1 else 0 end),
科目成绩2 = sum( case when 科目成绩2>100 then 1 else 0 end),
......
from tableA
union all
select '30分以上个数',
科目成绩1 = sum( case when 科目成绩1>30 then 1 else 0 end),
科目成绩2 = sum( case when 科目成绩2>30 then 1 else 0 end),
......
from tableB
(2)SELECET 姓名,科目成绩1,科目成绩2.... FROM ( select top 5 总分,* FROM TABLEA GROUP BY 班级 ORDER BY 总分 DESC)GROUP BY 班级 ORDER BY 总分 DESC
(3)SELECET AVG(总分) AS 总分,班级 FROM ( select top 5 总分,* FROM TABLEA GROUP BY 班级 ORDER BY 总分 DESC)GROUP BY 班级 ORDER BY AVG(总分) AS 总分 DESC
(2)select * from tbNAME a where (select count(1) from tbname where class=a.class and score>a.score)<5
如果 第四名后 有两个总分相同的 ,第5名你去哪一个呢,可以让两个并列第5名吗?
不好意思,如果有三个分数相同的,那就没完没了了.算我没说.