删除SQL SELECT中交叉的结果

Question

我正在连接同一个表的两个实例，以查看匹配相同序列号和日期但具有不同ID的记录。目的是识别冲突。我的查询是:

SELECT DISTINCT
t1.id,
t1.status,
t1.date,
t1.serial,
t2.id_2,
t2.status_2,
t2.date_2,
t2.serial_2
FROM table AS t1 INNER JOIN table AS t2 ON t1.serial = t2.serial_2
WHERE t1.date = t2.date_2 AND t1.id <> t2.id_2
ORDER BY t1.date, t1.id;

查询结果如下:

<表类>id状态日期串行id_2status_2date_2serial_2tbody><<tr>01有效的01/13/2021000102有效的01/13/2021000102有效的01/13/2021000101有效的01/13/2021000103号有效的02/10/2021000504有效的02/10/2021000504有效的02/10/2021000503有效的02/10/2021000505有效的02/15/2021000706有效的02/15/2021000706年有效的02/15/2021000705有效的02/15/20210007

您可以比较id为<而不是><>

SELECT 
t1.id,
t1.status,
t1.date,
t1.serial,
t2.id_2,
t2.status_2,
t2.date_2,
t2.serial_2
FROM table AS t1 
INNER JOIN table AS t2 
ON t1.serial = t2.serial_2
AND t1.date = t2.date_2 
AND t1.id < t2.id_2
ORDER BY t1.date, t1.id;

也可能你不需要distinct，这取决于你的数据看起来如何。