有三张表:
学生表student(stu_id, name)
成绩表scores(stu_id, subject, score)
补考成绩表(stu_id, subject, score)
其中:
stu_id:ID
name:姓名
subject:课程
score:成绩
输出:
姓名、课程和成绩 (没有补考,以成绩表为准,有补考,以成绩表和补考成绩表的较大者为准)
[size=medium]1. 准备表和数据[/size]
这里以MySQL数据库为例
[size=medium]2. 查询语句:[/size]
第一种思路:
a. 成绩表和补考成绩表左联接,利用CASE WHEN来决定输出哪个成绩。
b. 然后和学生表左联接,获取对应的姓名
利用MySQL的GREATEST(value1,value2,...),注意,任意一个参数为NULL,结果即为NULL。CASE块可以改写成
第二种思路:
a. 成绩表和补考成绩表联合,利用GROUP BY对学生和课程进行分组,利用MAX函数来获取最好的成绩。
b. 将a的结果作为一个表和学生表左联接,获取对应的姓名
[size=medium]3. 输出:[/size]
+----------+---------+-------+
| name | subject | score |
+----------+---------+-------+
| 张三 | 数学 | 78 |
| 李四 | 语文 | 80 |
| 李四 | 英语 | 68 |
| 王五 | 语文 | 57 |
| 王五 | 数学 | 59 |
| 张三 | 语文 | 60 |
| 李四 | 数学 | 65 |
| 张三 | 英语 | 70 |
| 王五 | 英语 | 79 |
+----------+---------+-------+
学生表student(stu_id, name)
成绩表scores(stu_id, subject, score)
补考成绩表(stu_id, subject, score)
其中:
stu_id:ID
name:姓名
subject:课程
score:成绩
输出:
姓名、课程和成绩 (没有补考,以成绩表为准,有补考,以成绩表和补考成绩表的较大者为准)
[size=medium]1. 准备表和数据[/size]
这里以MySQL数据库为例
-- 创建学生表
CREATE TABLE `student` (
`stu_id` TINYINT NOT NULL,
`name` varchar(8) NOT NULL,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 创建成绩表
CREATE TABLE `scores` (
`stu_id` TINYINT NOT NULL,
`subject` varchar(16) NOT NULL,
`score` TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 创建补考成绩表
CREATE TABLE `scores_sup` (
`stu_id` TINYINT NOT NULL,
`subject` varchar(16) NOT NULL,
`score` TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 插入测试数据
INSERT INTO `student`(
stu_id,
name
)
VALUES
('1', '张三'),
('2', '李四'),
('3', '王五');
INSERT INTO `scores`(
stu_id,
subject,
score
)
VALUES
('1', '语文', 60),
('2', '数学', 65),
('1', '英语', 70),
('1', '数学', 56),
('2', '语文', 49),
('2', '英语', 58),
('3', '语文', 57),
('3', '数学', 54),
('3', '英语', 79);
INSERT INTO `scores_sup`(
stu_id,
subject,
score
)
VALUES
('1', '数学', 78),
('2', '语文', 80),
('2', '英语', 68),
('3', '语文', 53),
('3', '数学', 59);
[size=medium]2. 查询语句:[/size]
第一种思路:
a. 成绩表和补考成绩表左联接,利用CASE WHEN来决定输出哪个成绩。
b. 然后和学生表左联接,获取对应的姓名
SELECT
s.name,
a.subject,
CASE
WHEN a.score >= 60 -- 成绩通过,以成绩表为准
THEN a.score
WHEN a.score < b.score -- 成绩没有补考的成绩高,以补考成绩为准
THEN b.score
ELSE a.score -- 成绩比补考的成绩高,以成绩表为准
END AS score
FROM
scores a
LEFT JOIN
scores_sup b -- 成绩表左联接补考表(补考表中可能没有成绩表中的记录)
ON
a.stu_id = b.stu_id
AND a.subject = b.subject
LEFT JOIN
student s -- 左联接学生表
ON
a.stu_id = s.stu_id;
利用MySQL的GREATEST(value1,value2,...),注意,任意一个参数为NULL,结果即为NULL。CASE块可以改写成
CASE
WHEN b.score IS NULL
THEN a.score
ELSE GREATEST(a.score, b.score)
END AS score
第二种思路:
a. 成绩表和补考成绩表联合,利用GROUP BY对学生和课程进行分组,利用MAX函数来获取最好的成绩。
b. 将a的结果作为一个表和学生表左联接,获取对应的姓名
SELECT
t.name,
s.subject,
s.score
FROM
(
SELECT
stu_id,
subject,
MAX(score) AS score
FROM
(
SELECT
*
FROM
scores
UNION
SELECT
*
FROM
scores_sup) u -- 成绩表和补考表联合
GROUP BY
stu_id,
subject) s -- 分组
LEFT JOIN
student t -- 左联接学生表
ON
s.stu_id = t.stu_id;
[size=medium]3. 输出:[/size]
+----------+---------+-------+
| name | subject | score |
+----------+---------+-------+
| 张三 | 数学 | 78 |
| 李四 | 语文 | 80 |
| 李四 | 英语 | 68 |
| 王五 | 语文 | 57 |
| 王五 | 数学 | 59 |
| 张三 | 语文 | 60 |
| 李四 | 数学 | 65 |
| 张三 | 英语 | 70 |
| 王五 | 英语 | 79 |
+----------+---------+-------+