如何从两个表中删除对等记录

Question

我有两个这样的表：

表1

id      serial_id product_id
59867   152905  10005646
59871   152900  10005646
168832  147721  10005646
220695  147721  10005646

表2

211399  152900
220248  147721

我想保留这些记录：

serial_id
152905
147721 

我的查询：

SELECT REC.*
FROM Table1 REC
LEFT JOIN Table2 REM ON REC.serial_id = REM.serial_id
WHERE
REM.serial_id IS NULL;

但我的查询返回的结果是：

id      serial_id product_id
59867   152905  10005646

但在第一个表中，我有两条serial_id: 147721的记录我想对等地检查这些序列，结果必须是：

id      serial_id product_id
59867   152905  10005646
220695  147721  10005646

Answer 1

要获得所需结果，还需要查看两个表中每个序列id的行号，并将其添加到JOIN中。这样，您就可以将Table1中的每一行与Table2中的一行进行匹配，并避免删除具有serial_id = 147721的两行。由于您使用的是MariaDB 10.3，因此可以使用CTEs和ROW_NUMBER():

WITH T1 AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY serial_id ORDER BY id) AS rn
FROM Table1
),
T2 AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY serial_id ORDER BY id) AS rn
FROM Table2
)
SELECT T1.id, T1.serial_id, T1.product_id
FROM T1
LEFT JOIN T2 ON T2.serial_id = T1.serial_id AND T2.rn = T1.rn
WHERE T2.id IS NULL

输出：

id      serial_id   product_id
220695  147721      10005646
59867   152905      10005646

dbfiddle 演示

Answer 2

将LEFT JOIN更改为INNER JOIN即可完成此操作。

从表1 REC中选择REC.，REM.INNER JOIN TAB2 REM ON REC.serial_id=REM.serial_id；

当您使用LEFT JOIN，然后添加条件REM.serial_id IS NULL时，它将删除匹配的记录，即serial_id为152900和147721的记录，而对于serial为152905的记录来说，在表2中找不到匹配记录，因此该查询的表2列将具有NULL serial _id。检查这个链接，在这个第一个查询中是你正在使用的，但没有where子句，第二个是我建议的。