面試題：問題1、查詢每個學期每門課程最高分記錄(包含全部5個欄位) 問題2、查詢與前一個學期相比成績有提高的記錄(包含全部5個欄位)

問題：

有如下分數表score

id	userid	course	score	term
1	Zhangsan	數學	80	2015
2	Lisi	語文	90	2016
3	Wangwu	化學	70	2017
4	Zhangsan	語文	80	2015
5	Zhangsan	化學	90	2015
6	Zhangsan	語文	90	2016

編寫sql完成如下查詢，一次查詢實現最好，也可寫多次查詢實現。

a)、查詢每個學期每門課程最高分記錄(包含全部5個欄位)

b)、查詢與前一個學期相比成績有提高的記錄(包含全部5個欄位)

答案：

a)、

SELECT
	s.id,
	s.userid,
	s.course,
	s.score,
	s.term 
FROM
	score s
	JOIN ( SELECT course, max( score ) maxScore, term FROM score GROUP BY term, score ) a 
WHERE
	s.term = a.term 
	AND s.course = a.course 
	AND s.score = a.maxScore 
ORDER BY
	s.term

解釋：

同一年同科目可能有多個最高分

b)、

SELECT
	b.id,
	b.userid,
	b.course,
	b.score,
	b.term
FROM
	score a
	JOIN score b ON a.userid = b.userid 
	AND a.course = b.course 
	AND b.term - a.term = 1 
	AND b.score > a.score

解釋：

自連線

建表語句：

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `id` int(10) NOT NULL,
  `userid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `course` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` int(3) NULL DEFAULT NULL,
  `term` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `score` VALUES (1, 'Zhangsan', '數學', 80, '2015');
INSERT INTO `score` VALUES (2, 'Lisi', '語文', 90, '2016');
INSERT INTO `score` VALUES (3, 'Wangwu', '化學', 70, '2017');
INSERT INTO `score` VALUES (4, 'Zhangsan', '語文', 80, '2015');
INSERT INTO `score` VALUES (5, 'Zhangsan', '化學', 90, '2015');
INSERT INTO `score` VALUES (6, 'Zhangsan', '語文', 90, '2016');