第三题比较难,我自己写一天后来请教老师才写出来,记录一下解题思路:
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;