【急求】用sql语句求成绩表中各个科目大于平均值的人数

Question

1.
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74

2.
姓名 课程 分数
---------- ---------- -----------
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94

sql server 对于上面两种表分别求成绩表中各个科目大于平均值的人数！好像涉及到行转列了...

请高人指点，折腾了一晚上没睡觉也没折腾出来... 囧
答出来一定追加积分... (┬＿┬)

Answer 1

你这样肯定是不行的。
where 中不能用聚合函数嵌套的。
不想用存储过程只有
select COUNT(1),名字 FROM 表1 where 语文> (select AVG(语文) FROM 表1) group by 名字
union
select COUNT(1),名字 FROM 表1 where 数学> (select AVG(数学) FROM 表1) group by 名字
Union
select COUNT(1),名字 FROM 表1 where 屋里> (select AVG(物理) FROM 表1) group by 名字

第二个表2：
select COUNT(1), a.名字, a.课程from 表2 a
inner join
(select AVG(分数) as g , b.课程from 表2 group by b.课程) b
on a.课程= b.课程
where a.分数> g
group by a.名字, a.课程
就可以看出你要的结果

Answer 2

对于表1可以使用结果合并

select count(*) as 语文人数 from [表1] where 语文 > avg(语文) union select 数学人数= count(*)from [表1] where 数学>avg(数学) select count(*) as 物理人数 from 表1 where 物理>avg(物理)

对于表2,由于求平均分涉及了一个分组,这样在使用中至少出现SQL语句的两层嵌套,所以建议你使用存储过程!

追问

where语句中不能用聚合函数吧。。。。
2不用存储过程，2层也可以，能说下思路么

追答

where条件应该是可以使用聚合函数的,但是不管是什么样的情况都不能聚合函数进行嵌套!orcle数据库中可以嵌套的,SQL子查询的层不能超过三层!第二个其实是二层三个SQL子查询的嵌套,共六个SQL,一般认为在条件中使用嵌套与子查询数据库性能不好,很少使用的!就是一个使用select 语句求出平均值,然后使用大于就可以了!因为子查询里还涉及分组,所以性能较低!

如果在SQL中不支持聚合函数的话,可以将聚合函数改成子查询与第二个就相同了!但不涉及分组的!比较下来还是使用存储过程比较好!不明白为什么你非要一句SQL语句呢?

declare @avg_yuwen double
select @avg_yuwen = avg(语文) from [表1]
select count(*) from [表1] where 语文>@avg_yuwen
要比
select count(*) from [表1] where 语文> avg(语文)
和
select count(*) from [表1] where 语文 > (select avg(语文) from [表1])
的性能都高

而select count(*) from [表2] where 语文> (select avg(语文) from [表2] group by 姓名) group by 姓名
就句就是求表2语文的,将三个语句联合就是二层的!你自己看一下我为什么建议你使用存储过程了!

Answer 3

试试看这样(在where 里面使用子查询)：
select count(*) as 语文人数 from [表1] where 语文 > (select avg(语文) from [表1]);

Answer 4

select distinct name from students where name not in(
select distinct name from students left join (
select kecheng,avg(fenshu) as avg_score from students group by kecheng
) tmp on students.kecheng=tmp.kecheng where students.fenshu<tmp.avg_score
);

Answer 5

select count(*) from 表2 group by 课程 having 分数>avg（分数）