info表,:classId,userId,score
1.找出每个班级里分数最高的人
2.找出平均分在60分以上的班级 并且按平均分排序
3.找出分数超过80分的人大于20人的班级
原题出处:
http://www.javaeye.com/topic/459851?page=1
数据模型以SQLServer2000为例
declare @temp table(
classId varchar(5),
userId varchar(10),
score tinyint
)
INSERT INTO @temp VALUES('1','001',70)
INSERT INTO @temp VALUES('1','002',50)
INSERT INTO @temp VALUES('1','003',90)
INSERT INTO @temp VALUES('2','001',100)
INSERT INTO @temp VALUES('2','002',90)
INSERT INTO @temp VALUES('3','001',90)
INSERT INTO @temp VALUES('3','002',100)
INSERT INTO @temp VALUES('3','003',150)
分析(1):
======条件:班级分组查询、分数高无法直接得到人名,最后查询条件变成两个不确定值,则必须使用联合查询。
SELECT t1.classId,t1.userId,t1.score FROM @temp t1,(SELECT classId,MAX(score) AS score FROM @temp GROUP BY classId) t2
WHERE t1.classId=t2.classId AND t1.score=t2.score
分析(2)
======条件:班级分组、求平均数可以直接查询得出班级名。
SELECT classId,AVG(score) AS score FROM @temp GROUP BY classId ORDER BY AVG(score) DESC
分析(3)
======条件:班级分组、人数大于20、分数超过80分的人,无法直接得到。但是该查询符合子查询条件,
因为其中两个重要条件(只有一个列为动态值),一个可以从条件中直接得到,一个则可以从子查询当中得到。
a:(找出班的总人数大于20,并且分数超过80分的人)
SELECT * FROM @temp WHERE score>80 AND classId IN
(SELECT classId FROM @temp GROUP BY classId HAVING COUNT(userId)>20)
b:(找出班级分数大于80,且该人数总和超过20的人)
SELECT * FROM @temp WHERE score>80 AND classId IN
(SELECT classId FROM @temp WHERE score>80 GROUP BY classId HAVING COUNT(userId)>20)