日期:2014-05-17 浏览次数:20605 次
/*主
dataid (主键)
studentid 学生id
gradecode 年级编码
从
itemid
dataid (外键)
projectid 项目id (外键)
value 检查值
Result 结果(0优秀;1良好;2及格;3不及格)
查询结果
年级编码 检查人数 平均值 最大值 最小值 优秀人数 良好人数 及格人数 不及格人数
谢谢
*/
select gradecode as 年级编码,
COUNT(studentid) 检查人数,
AVG(value) as 平均值,
max(value) as 最大值,
min(value) as 最小值,
COUNT(case when value > 85 then studentid else null end) as 优秀人数,
COUNT(case when value between 76 and 85 then studentid else null end) as 良好人数,
COUNT(case when value between 60 and 75 then studentid else null end) as 及格人数,
COUNT(case when value < 60 then studentid else null end) as 不及格人数
from tp
inner join tb
on tp.dataid = tb.dataid
where tp.projectid = xxx
select a.gradecode '年级编码',
count(1) '检查人数',
avg(b.value) '平均值',
max(b.value) '最大值',
min(b.value) '最小值',
sum(case when b.value>85 then 1 else 0 end) '优秀人数',
sum(case when b.value>=76 and b.value<=85 then 1 else 0 end) '良好人数',
sum(case when b.value>=60 and b.value<=75 then 1 else 0 end) '及格人数',
sum(case when b.value<60 then 1 else 0 end) '不及格人数'
from [主表] a
inner join [从表] b on a.dataid=b.dataid
where b.projectid=[某个项目id]
group by a.gradecode