单表操作
1.单字段条件分组查询
//获取同名的人的详细信息
//获取同分数的人的详细信息
//获取同地区的人的姓名
分析:获取相同的字段,字段条件和分组条件一致,通过where的字段可以获取到条目,属于单字段条件查询
解://获取同名的人的详细信息
SELECT * FROM test1 WHERE NAME IN (SELECT NAME FROM test1 GROUP BY NAME HAVING COUNT(NAME)>1)
2.多字段条件分组查询
//获取每个地区的最高分的人的详细信息
//获取各科目的最高分的详细信息
分析:第一反应:和第一题几乎一致,分组后的聚合函数条件不同而已,但是属于双字段条件,通过最高分不能确认条目,通过地区(科目)也不能确认条目,所以无法使用in,要关联查询
SELECT * FROM test1 INNER JOIN (SELECT str,MAX(age) oage FROM test1 GROUP BY str) o ON test1.str=o.str AND test1.age=o.oage
多表操作
将第一个表字段含义拆分出一个成绩表
userid对应test1.id,grade为成绩,subject为科目
//获取每个地区的最高分的人的详细信息
//获取各科目的最高分的详细信息
分析:看到多表就慌,实际和单表一致,拿到各科目的最高分就能拿到用户详细信息,单表查询加子查询,或者关联查询就可以了,拿到上面单表的代码改装
SELECT * FROM cource INNER JOIN (SELECT SUBJECT,MAX(grade) ograde FROM cource GROUP BY SUBJECT) o ON cource.SUBJECT=o.SUBJECT AND cource.grade=o.ograde
此时关联获取用户详细信息就明朗了
SELECT * FROM test1 ,
(
SELECT id,userid,grade,o.subject FROM cource INNER JOIN (SELECT SUBJECT,MAX(grade) ograde FROM cource GROUP BY SUBJECT) o
ON cource.SUBJECT=o.SUBJECT AND cource.grade=o.ograde)
v WHERE test1.id=v.userid
分组获取组内其他字段的应用,oracle或db2的 PARTITION BY真的好用,可以获取分组的组内排名信息,然后条件判断新字段(组内排序)即可
SELECT *,rank() over(PARTITION BY str ORDER BY age desc) my_age FROM USER where my_age=1