mysql三表查询，我是初学者

mysql中的三表查询，使用外连和查找最大值和最大值分组完成

img

img

img

img


要求是查询出各科成绩的最高分和考生，展示科目、最高分和考生名称

SELECT subject.subject, MAX(grade.score) as highest_score, student.name
FROM grade
JOIN student ON grade.student_id = student.id
JOIN subject ON grade.subject_id = subject.id
GROUP BY subject.subject
不知道你这个问题是否已经解决, 如果还没有解决的话:
  • 这有个类似的问题, 你可以参考下: https://ask.csdn.net/questions/7656026
  • 我还给你找了一篇非常好的博客，你可以看看是否有帮助，链接：mysql查询某个用户在某条记录中是否分数最高(比较同一条数据中指定列数据的大小)
  • 你还可以看下mysql参考手册中的 MySQL 错误信息和常见的问题 字符串搜索中的大小写敏感度
  • 除此之外, 这篇博客: mysql练习之学生表教师表课程表成绩表中的 – 18.查询各科成绩最高分、最低分和平均分： 部分也许能够解决你的问题, 你可以仔细阅读以下内容或者直接跳转源博客中阅读:

    以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
    –及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

    SELECT 
	sc.c_id AS '课程ID',
	c.c_name AS '课程name',  
	MAX(sc.s_score) AS '最高分',
	MIN(sc.s_score) AS '最低分',
	AVG(sc.s_score) AS '平均分',
	SUM(CASE WHEN sc.s_score >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS '及格率',
	SUM(CASE WHEN sc.s_score >= 70 AND sc.s_score < 80 THEN 1 ELSE 0 END) / COUNT(*) AS'中等率',
	SUM(CASE WHEN sc.s_score >= 80 AND sc.s_score < 90 THEN 1 ELSE 0 END) / COUNT(*) AS'优良率',
	SUM(CASE WHEN sc.s_score >= 90 THEN 1 ELSE 0 END) / COUNT(*) AS'优秀率'
FROM
score sc, course c
WHERE sc.c_id = c.c_id
GROUP BY sc.c_id 

-- 01	语文	80	31	64.5	0.6667	0.3333	0.3333	0
-- 02	数学	90	30	72.6667	0.8333	0	0.5	0.1667
-- 03	英语	99	20	68.5	0.6667	0	0.3333	0.3333

-- 格式优化
SELECT CONCAT(FORMAT(0.665269* 100,2),'%')

	-- 19、按各科成绩进行排序，并显示排名(实现不完全)

SELECT 
	sc.c_id AS '课程ID',
	c.c_name AS '课程name',  
	MAX(sc.s_score) AS '最高分',
	MIN(sc.s_score) AS '最低分',
	AVG(sc.s_score) AS '平均分',
CONCAT(FORMAT((SUM(CASE WHEN sc.s_score >= 60 THEN 1 ELSE 0 END) / COUNT(*))* 100,2),'%') 	 AS '及格率',
CONCAT(FORMAT((	SUM(CASE WHEN sc.s_score >= 70 AND sc.s_score < 80 THEN 1 ELSE 0 END) / COUNT(*))* 100,2),'%') AS'中等率',
CONCAT(FORMAT((SUM(CASE WHEN sc.s_score >= 80 AND sc.s_score < 90 THEN 1 ELSE 0 END) / COUNT(*))* 100,2),'%')	 AS'优良率',
CONCAT(FORMAT((SUM(CASE WHEN sc.s_score >= 90 THEN 1 ELSE 0 END) / COUNT(*))* 100,2),'%')	 AS'优秀率'
FROM
score sc, course c
WHERE sc.c_id = c.c_id
GROUP BY sc.c_id 


-- 01	语文	80	31	64.5	66.67%	33.33%	33.33%	0.00%
-- 02	数学	90	30	72.6667	83.33%	0.00%	50.00%	16.67%
-- 03	英语	99	20	68.5	66.67%	0.00%	33.33%	33.33%
  • 您还可以看一下 李胜金老师的MySQL 高级性能优化架构实战篇 千万级高并发交易一致性系统基础课程中的 数据库索引介绍小节, 巩固相关知识点
  • 以下回答来自chatgpt:

    首先，根据问题描述，我们需要进行三表查询，同时使用外连接和查找最大值的方法来查询各科成绩的最高分和对应的考生。我们可以按照以下步骤解决该问题：

    1. 创建表结构和插入测试数据：
    CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL
);

CREATE TABLE subjects (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(50) NOT NULL
);

CREATE TABLE scores (
    score_id INT PRIMARY KEY,
    student_id INT,
    subject_id INT,
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);

INSERT INTO students (student_id, student_name) VALUES
(1, '张三'),
(2, '李四'),
(3, '王五');

INSERT INTO subjects (subject_id, subject_name) VALUES
(1, '数学'),
(2, '英语'),
(3, '物理');

INSERT INTO scores (score_id, student_id, subject_id, score) VALUES
(1, 1, 1, 80),
(2, 1, 2, 90),
(3, 1, 3, 85),
(4, 2, 1, 75),
(5, 2, 2, 85),
(6, 2, 3, 90),
(7, 3, 1, 90),
(8, 3, 2, 95),
(9, 3, 3, 80);
    1. 编写SQL语句进行三表查询，使用外连接和查找最大值分组：
    SELECT s.subject_name, MAX(score) AS max_score, st.student_name
FROM students st
LEFT JOIN scores sc ON st.student_id = sc.student_id
LEFT JOIN subjects s ON sc.subject_id = s.subject_id
GROUP BY s.subject_name;

    这条SQL语句使用了外连接（LEFT JOIN）将学生表、成绩表和科目表连接起来。然后使用了MAX函数来计算每个科目的最高分，并将结果命名为max_score。最后使用GROUP BY对科目名进行分组。

    通过以上步骤，我们可以得到科目、最高分和对应的考生名称的查询结果。


如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^