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既要被遍历,又要在循环体中修改自己,就会报错)
 

  • 3
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值