非常实用的sql思路 一题6解
先看2张表student
score
CREATE TABLE `score` (
`id` int NOT NULL AUTO_INCREMENT,
`stu_id` int NOT NULL,
`c_name` varchar(20) DEFAULT NULL,
`grade` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL,
`sex` varchar(4) DEFAULT NULL,
`birth` year DEFAULT NULL,
`department` varchar(20) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `score` VALUES (1, 901, '计算机', 98);
INSERT INTO `score` VALUES (2, 901, '英语', 80);
INSERT INTO `score` VALUES (3, 902, '计算机', 65);
INSERT INTO `score` VALUES (4, 902, '中文', 88);
INSERT INTO `score` VALUES (5, 903, '中文', 95);
INSERT INTO `score` VALUES (6, 904, '计算机', 70);
INSERT INTO `score` VALUES (7, 904, '英语', 92);
INSERT INTO `score` VALUES (8, 905, '英语', 94);
INSERT INTO `score` VALUES (9, 906, '计算机', 90);
INSERT INTO `score` VALUES (10, 906, '英语', 85);
INSERT INTO `student` VALUES (901, '张老大', '男', 1995, '计算机系', '北京市海淀区');
INSERT INTO `student` VALUES (902, '张老二', '男', 1996, '中文系', '北京市昌平区');
INSERT INTO `student` VALUES (903, '张三', '女', 2000, '中文系', '湖南省永州市');
INSERT INTO `student` VALUES (904, '李四', '男', 2000, '英语系', '辽宁省阜新市');
INSERT INTO `student` VALUES (905, '王五', '女', 2001, '英语系', '福建省厦门市');
INSERT INTO `student` VALUES (906, '王六', '男', 1998, '计算机系', '湖南省衡阳市');
题目:查询同时参加计算机和英语考试的学生的信息
解法1: 行转列的应用 取得同时有考计算机和英语的学生
SELECT stu_id ,
MAX(CASE c_name WHEN '计算机' THEN grade ELSE 0 END ) 计算机,
MAX(CASE c_name WHEN '英语' THEN grade ELSE 0 END ) 英语
FROM score
GROUP BY stu_id;
解法2: JOIN合并成2列 取得同时有考计算机和英语的学生
SELECT * FROM score s1 JOIN score s2
ON s1.stu_id = s2.stu_id WHERE
s1.c_name = '计算机' AND s2.c_name = '英语'
解法3: 分组计数法
select * from score
WHERE c_name = '计算机' OR c_name = '英语'
GROUP BY stu_id
HAVING count(*) = 2
解法4: 手动增长计数列
SELECT stu_id,c_name,IF (@pre=stu_id,@rownum :=@rownum+1,@rownum :=1),@pre :=stu_id FROM score s,
(SELECT @pre :='',@rownum :=0) b WHERE s.c_name='计算机' OR s.c_name='英语'
解法5: 开窗排序
SELECT*,row_number () OVER (PARTITION BY stu_id ORDER BY id) AS num FROM score WHERE c_name='计算机' OR c_name='英语'
解法6: 最熟悉的子查询
SELECT * FROM student where
id IN(select stu_id FROM score
where stu_id IN(select stu_id FROM score
where c_name = '计算机' ) AND c_name = '英语')