阅读 MySQL知识1

以下几个问题都在 t_socre 表中进行，表结构如下。

CREATE TABLE `t_score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `class` varchar(255) DEFAULT NULL,
  `score` decimal(11,2) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

① 查询第二高的分数

查第一高的很简单，一个 MAX 函数好了，那查询第二高的呢，稍微改变一下就可以了。

使用子查询 + MAX，NOT IN 还可以替换为 <> 、!=

SELECT MAX(score) FROM t_score WHERE score NOT IN (select MAX(score) from t_score );

使用 < + MAX，和上一种方式其实差不多

SELECT MAX(score) FROM t_score  WHERE score < (SELECT MAX(score) FROM t_score);

使用 自查询 + Limit

SELECT score  FROM (SELECT score FROM t_score ORDER BY score DESC LIMIT 2) AS s ORDER BY score ASC LIMIT 1;

② 查找每个班的最高分

一个 GROUP BY 解决

SELECT class,MAX(score) FROM t_score GROUP BY class;

③ 查找重复记录

例如下面这条 SQL，只查询不重复的记录以及重复记录中id最大的一条，因为字查询用的是 MAX(id)。

稍加改动，将MAX(id)改为 MIN(id)，就可以查询重复记录中id最小的一条了。

SELECT * FROM t_score a 
WHERE
	id = 
	(
	SELECT MAX(id) FROM	t_score b 
	WHERE
		a.class = b.class 
		AND a.`name` = b.`name` 
	AND a.score = b.score 
	)

④ 删掉多余重复记录

大多数情况下，查询重复记录不是目的，最终的目的很有可能是将多余的重复记录删掉，只留一条就够了。

稍加改动是不是就可以了呢，例如下面的语句。最开始我就是这么写的，而且点击执行后，悠然的拿起手机，准备劳逸结合一下。

DELETE FROM t_score a 
WHERE
	id != (
	SELECT MAX(id) FROM	t_score b 
	WHERE
		a.class = b.class 
		AND a.`name` = b.`name` 
		AND a.score = b.score 
	);

报错：

1093 - You can't specify target table 'a' for update in FROM clause, Time: 0.028000s

不能在 SELECT 子句中引用正在被更新的表，显然我们要更新的表就被用在了子查询中。

那怎么办呢，稍微进一步思考，只要把要删除记录的 id 集合先查出来，然后再删除 id 在待删除集合中的就可以了，就是再套一层。

DELETE FROM t_score WHERE id IN (
    SELECT id FROM (
        SELECT id FROM t_score a 
        WHERE id != (
            SELECT MAX(id) 
            FROM t_score b 
            WHERE a.class = b.class 
                AND a.`name` = b.`name` 
                AND a.score = b.score
        )
    ) AS temp
);