sql语句，分组后，组内根据datetime字段排序，保留日期最新的一条组内记录，删除其他项。（用于组内重复数据的剔除）

一、背景：

表pipeline_data，有自增主键line_id，由于多次导入.mdb文件，导致exp_no存在重复的数据，业务上面需要一个exp_no只保留一条最新的数据，即updatetime字段最新。如果同一个exp_no下有三条记录，且updatetime都相同，也要只保留一条记录，删除其他多余的两项。

二、总体思想：

先用exp_no分组，再组内只保留updatetime最新的一项，其他项删除；若组内的最新updatetime存在多项，只保留一项即可。

三、解决方案：

1.本地使用的是mysql5.6或mysql5.7。

2.网上有mysql8.0的对应方法，sql编码差别比较大，主要是引入了row_number()方法，方便分组和组内排序、过滤等。参考代码如下：（这个我没试过）

SELECT
	a.Classid,
	a.English
FROM
	(
		SELECT
			Classid,
			English,
			row_number () over (
				PARTITION BY Classid
				ORDER BY
					English DESC
			) AS n
		FROM
			CJ
	) a
WHERE
	n <= 2

官方推荐的方式，

row_number()从1开始，为每一条分组记录返回一个数字，

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。

3.下面是本文的方法实践过程，包含核心sql语句。

 

 

 

已知：line_id是自增主键，数据可能存在多次插入的情况，同一个exp_no可能对应多条记录；

要求：一个exp_no只保留一条记录，且是updatetime最新的一条记录，其余要删除。

 核心SQL语句如下，

DELETE
FROM
	pipeline_data
WHERE
	line_id IN (
		SELECT
			y.line_id AS y_id
		FROM
			(
				SELECT
					w.line_id AS line_id
				FROM
					pipeline_data w
				WHERE
					w.exp_no IN (
						SELECT
							p.exp_no AS exp_no
						FROM
							(
								SELECT
									z.exp_no AS exp_no
								FROM
									(
										SELECT
											k.exp_no AS exp_no
										FROM
											pipeline_data k
										GROUP BY
											k.exp_no
										HAVING
											COUNT(1) > 1
									) z
							) p
					)
			) y
	)
AND line_id NOT IN (
	SELECT
		j.line_id AS j_id
	FROM
		(
			SELECT
				e.line_id AS line_id
			FROM
				(
					SELECT
						u.line_id AS line_id
					FROM
						(
							SELECT
								g.line_id AS line_id,
								g.exp_no AS g_exp_no,
								MAX(g.updatetime) AS max_g_updatetime
							FROM
								pipeline_data g
							GROUP BY
								g_exp_no
						) u
				) e
		) j
)

 

四、错误场景解析：

1.同一张表嵌套查询报错：[Err] 1248 - Every derived table must have its own alias

 原因分析：子查询的结果要进行第二次查询，必须给子查询取别名，才可以查询，也就是把子查询的结果集放到临时表里面。

 

 2.同一张表子查询后增删改报错：[Err] 1093 - You can't specify target table 'pipeline_data' for update in FROM clause

原因解析：

[Err] 1093 - 不能在FROM子句中指定目标表"pipeline_data"进行更新。

原因分析：如果在增删改语句中，嵌套了同一张表子查询的形式进行增删改，那么应该把这个子查询的结果集先取别名，也就是保存在临时表中，再进行外层的增删改，才可以执行。外层操作实际上是在第一次select的结果集临时表中增删改。（否则，有一种内外死循环的感觉，子select是父操作的条件，先执行子select，已经锁定表pipeline_data，是无法执行父操作的；java里面的for循环也有类似场景，同一个list既要被遍历，又要在循环体中修改自己，就会报错）