One 、 background ：

surface pipeline_data, There is a self increasing primary key line_id, Due to multiple imports .mdb file , Lead to exp_no Duplicate data exists , Business needs a exp_no Keep only one piece of the latest data , namely updatetime The field is up to date . If the same exp_no There are three records , And updatetime All the same , Keep only one record , Delete the other two extra items .

Two 、 The general idea ：

First use exp_no grouping , Only updatetime The latest one , Delete other items ; If the latest in the Group updatetime There are multiple , Just keep one item .

3、 ... and 、 Solution ：

1. What is used locally is mysql5.6 or mysql5.7.

2. Online mysql8.0 The corresponding method of ,sql The coding difference is quite large , Mainly introduced row_number() Method , Convenient grouping and sorting within groups 、 Filtration, etc. . The reference codes are as follows ：（ I haven't tried this ）

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

Official recommendations ,

row_number() from 1 Start , Return a number for each grouped record ,

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) Express basis COL1 grouping , Within the group according to COL2 Sort , The value calculated by this function represents the sequence number of each group after internal sorting ( The only continuous ).

3. The following is the method practice process of this paper , Including the core sql sentence .

It is known that ：line_id It's an auto primary key , Data may be inserted many times , The same exp_no It may correspond to multiple records ;

requirement ： One exp_no Keep only one record , And is updatetime The latest record , The rest should be deleted .

  The core SQL The statement is as follows ,

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
)

Four 、 Error scenario analysis ：

1. The nested query of the same table reports an error ：[Err] 1248 - Every derived table must have its own alias

  Cause analysis ： The results of the sub query should be queried for the second time , The subquery must be aliased , You can query , That is, put the result set of the sub query into the temporary table .

 2. Add, delete, modify and report errors after querying the same table ：[Err] 1093 - You can't specify target table 'pipeline_data' for update in FROM clause

Cause analysis ：

[Err] 1093 - Can't be in FROM Clause specifies the target table "pipeline_data" updated .

Cause analysis ： If in the addition, deletion and modification statement , Nested the same table in the form of sub query to add, delete and modify , Then the result set of this sub query should be aliased first , That is, it is saved in the temporary table , Then add, delete and modify the outer layer , To execute . The outer operation is actually the first select Add, delete and modify in the temporary table of the result set of .（ otherwise , There is a sense of internal and external dead circulation , Son select Is the condition of the parent operation , Execute child first select, The table has been locked pipeline_data, Cannot perform parent operation ;java Inside for There are similar scenarios in the loop , The same list It must be traversed , You have to modify yourself in the loop body , You're going to report a mistake ）