sql回答

非标准答案
-- 2.1 查询出每门课都大于60分的学姓名


SELECT user_id FROM score WHERE score > 60 GROUP BY user_id HAVING COUNT(1)  >2;

-- 解答

SELECT user_name FROM user WHERE user_id IN (SELECT user_id FROM score  WHERE score > 60 GROUP BY user_id HAVING COUNT(1) >2);

-- 2.2 查询出总分大于220分的 学生姓名，与总分数

SELECT user_id,SUM(score) FROM score  GROUP BY user_id HAVING SUM(score)  >220;

-- 解答

SELECT user_name FROM user WHERE user_id IN (SELECT user_id FROM score   GROUP BY user_id HAVING SUM(score) >220);

-- 2.3 用sql语句把每个学生的总分数更新到User用户表 的user_score 学生总分数 字段里

-- 解答

UPDATE `user` u  SET user_score = (SELECT SUM(score)  FROM score WHERE  user_id = u.user_id  GROUP BY user_id );

-- 2.4 查询出学生的 班级名称、姓名、课程名称、分数

SELECT c.class_name,u.user_name,s.course,s.score FROM user u JOIN score s  ON u.user_id = s.user_id JOIN class c ON c.class_id = u.class_id;

-- 2.5 查询出每个学生的班级、姓名、平均分、最高分数、最低分数,保留两位小数

SELECT user_id, ROUND(avg(score),2) ascore , max(score) xscore , min (score)iscore FROM score GROUP BY user_id;

-- 解答
SELECT c.class_name,u.user_name,a.ascore '平均分', a.xscore '最高分 数',a.iscore '最低分数'
FROM user u JOIN class c ON c.class_id = u.class_id
JOIN (
SELECT user_id, ROUND(avg(score),2) ascore , max(score) xscore , min (score)iscore FROM score GROUP BY user_id
) a
ON a.user_id = u.user_id;

-- 2.6 查询出学生的 班级名称、最高分的课程、最高分数

-- 查询每个user_id 最高分数
SELECT user_id,max(score) xscore FROM  score GROUP BY user_id;

-- 查询每个user_id 最高分数 最高分的课程  s1
SELECT s.user_id,course,xscore FROM  score s JOIN
(SELECT user_id,max (score) xscore FROM  score GROUP BY user_id)a
ON a.user_id = s.user_id  WHERE score = a.xscore;

-- 查询出学生的 班级名称 user_id          s2
SELECT c.class_name,u.user_id,u.user_name FROM user u JOIN class c ON  u.class_id = c.class_id;

-- 解答a
SELECT s2.class_name,s2.user_name,s1.course 最高分课程,s1.xscore 最高分  FROM
(
SELECT s.user_id,course,xscore FROM  score s JOIN
(SELECT user_id,max (score) xscore FROM  score GROUP BY user_id)a
ON a.user_id = s.user_id  WHERE score = a.xscore
) s1
JOIN (
SELECT c.class_name,u.user_id,u.user_name FROM user u JOIN class c ON  u.class_id = c.class_id
) s2
ON s1.user_id = s2.user_id;

-- 解答b
select b.class_name,b.user_name,b.d 最高分,g.course 最高分课程 from score g
join(
select c.class_name,a.user_name,a.d from class c
   join(
    select u.class_id,s.user_id,u.user_name,max(score) d from score s left join  user u on s.user_id=u.user_id group by s.user_id
)a on a.class_id=c.class_id
)b

on b.d=g.score ;