mysql - 过滤重复记录，保留1条记录

Question

我能够获取重复记录，并且希望根据创建日期(最旧的)为每个项目条形码保留 1 条记录

获取重复记录的示例脚本:

select pl5.refcode, pl5.barcode, pl5.name, pl5.status, pl5.createDate
from pList6 pl6
inner join plist5 pl5 on pl6.barcode = pl5.barcode
order by pl5.barcode, pl5.createDate;

输出:

**refcode      barcode          ItemDescription       status        createDate**
0075345        480000234512     Shampoo A             A             2017-09-22 10:12:29
0002345        480000234512     Shampoo A             A             2019-09-22 13:07:29
0010388        790000345217     Shampoo B             A             2018-05-13 08:34:01
0023754        790000345217     Shampoo B             A             2019-10-17 09:45:11

<小时/>

最终输出应如下所示:(保留 2 个重复条形码中最旧创建的项目)

**refcode      barcode          ItemDescription       status        createDate**
0075345        480000234512     Shampoo A             A             2017-09-22 10:12:29
0010388        790000345217     Shampoo B             A             2018-05-13 08:34:01

Answer 1

我想您需要根据每个条形码的创建日期进行第一个记录。如果您使用 MySQL 8.0 或更高版本，您可以使用窗口函数 -

SELECT refcode, barcode, ItemDescription, status, createDate
FROM (SELECT refcode, barcode, ItemDescription, status, createDate, ROW_NUMBER() OVER(PARTITION BY barcode ORDER BY createDate) RN
      FROM pList6 pl6
      inner join plist5 pl5 on pl6.barcode = pl5.barcode) RANK
WHERE RN = 1;

如果您使用的是较低版本，您可以尝试以下 -

SELECT pl6.refcode, pl6.barcode, pl6.ItemDescription, pl6.status, pl6.createDate
FROM pList6 pl6
LEFT JOIN pList6 pl7 ON pl6.barcode = pl7.barcode
                     AND pl6.ItemDescription = pl7.ItemDescription
                     AND pl6.status = pl7.status
                     AND pl6.createDate < pl7.createDate
INNER JOIN plist5 pl5 ON pl6.barcode = pl5.barcode