学习日记——0822 mysql 查询习题

第三题比较难，我自己写一天后来请教老师才写出来，记录一下解题思路：
1.把子查询结果当成一个临时表，temp，然后从临时表中查找数据，

2.select temp.name,temp.score from，连接scores，拿到科目的id，

3.条件是id等于学生id,分数等于分数，这样就能查出科目的id

4.连表科目，LEFT JOIN course as co on sc2.c_id = co.id

查出科目的名称

第二种解法，先把成绩排序再分组，下面第五题就是用这种解法。






源码：
1.SELECT * FROM student JOIN scores on student.id=scores.s_id WHERE s_id=1

2.SELECT c_id,AVG(score),MAX(score) from scores GROUP BY c_id;

select temp.name,temp.score,co.name from
(SELECT
s.id,
s.name,
MAX(sc.score) as score
FROM student as s
LEFT JOIN scores as sc ON s.id=sc.s_id
GROUP BY s.id) as temp
LEFT JOIN scores as sc2
on temp.id = sc2.s_id and temp.score = sc2.score
LEFT JOIN course as co
on sc2.c_id = co.id
GROUP BY temp.id

把这个结果当成一个临时表，temp，然后从临时表中查找数据，

select temp.name,temp.score from，连接scores，拿到科目的id，

条件是id等于学生id,分数等于分数，这样就能查出科目的id

连表科目，LEFT JOIN course as co on sc2.c_id = co.id

查出科目的名称

法二：

SELECT p1.* FROM
(SELECT *
FROM scores as p0
ORDER BY p0.score DESC
LIMIT 1000
)p1
GROUP BY p1.s_id;

因为group by无法放到order by之后执行，所以需要创建虚表p1，limit 100000是取前100000条数据，因为不加limit虚表的排序不会生效，100000是乱写的，

是多少都行，只要足够取出要查询的数据条数。

https://www.cnblogs.com/darkclouds/p/12175682.html

SELECT student.name AS 姓名,course.name AS 课程,score
FROM student LEFT JOIN scores ON student.id=scores.s_id LEFT JOIN course ON scores.c_id=course.id
WHERE student.name LIKE “张%”;

SELECT p1.s_id,p1.score,s.name,s.age,s.gander FROM
(SELECT *
FROM scores as p0
ORDER BY p0.score DESC
LIMIT 1000
)p1
LEFT JOIN student as s ON p1.s_id = s.id
GROUP BY p1.c_id;

SELECT *
FROM student LEFT JOIN scores ON student.id=scores.s_id LEFT JOIN course ON scores.c_id=course.id
WHERE student.name LIKE “%张%” OR student.name LIKE “%李%” ORDER BY s_id;

SELECT id,name,age,gander,AVG(score)
FROM student LEFT JOIN scores ON student.id=scores.s_id
GROUP BY s_id HAVING AVG(score)>60;

SELECT id,name,age,gander,SUM(score)
FROM student LEFT JOIN scores ON student.id=scores.s_id
GROUP BY s_id ORDER BY SUM(score) DESC;