非常实用的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 = '英语')