MySQL含UNION与DISTINCT联表查询仍出现重复shipment_id问题

我编写了一段包含UNION和双字段DISTINCT的SELECT查询语句，但返回结果并未仅展示唯一数据，而是出现了重复的shipment_id，不清楚该如何修复，寻求解决思路。

原SQL语句 #

FROM
    cs.acct_sales_daily_extract2 sa
    inner JOIN
        ( 
               select distinct shipmentid, GPRecDate 
                from custom_cs.apj 
                where apdelay > 0 
                                                        
                union 
                
                select distinct shipmentid, 
                    GPRecDate 
                from custom_cs.arh 
                where ardelay > 0 
        ) shd on sa.shipment_id = shd.shipmentid

示例数据 #

1000162 173 173 TNME    85  20210706    IM  09735ZZ 13411   HOME DEPOT  0.00    0.00    90.00   90.00   -90.00  -90.00  0.0000  0.00    0.00    1000162         2021-07-06 21:11:27 482942  1000162 2021-07-06
1000162 173 173 TNME    85  20210618    IM  09735ZZ 13411   HOME DEPOT  3020.67 3020.67 3418.50 3418.50 -397.83 -397.83 -0.1317 0.00    1.00    1000162         2021-06-18 21:10:54 474954  1000162 2021-07-06
1000197 119 119 VAPT    100 20210630    TR  00426ZZ 10852   CV INTERNATIONAL    0.00    0.00    10.00   10.00   -10.00  -10.00  0.0000  0.00    0.00    1000197         2021-06-30 21:11:02 481508  1000197 2021-06-30
1000197 119 119 VAPT    100 20210620    TR  00426ZZ 10852   CV INTERNATIONAL    1365.40 1365.40 952.75  952.75  412.65  412.65  0.3022  0.00    1.00    1000197         2021-06-20 21:13:07 475387  1000197 2021-06-30
1000199 119 119 VAPT    100 20210630    TR  00426ZZ 10852   CV INTERNATIONAL    0.00    0.00    30.00   30.00   -30.00  -30.00  0.0000  0.00    0.00    1000199         2021-06-30 21:11:02 481507  1000199 2021-06-30
1000199 119 119 VAPT    100 20210620    TR  00426ZZ 10852   CV INTERNATIONAL    1500.40 1500.40 1072.75 1072.75 427.65  427.65  0.2850  0.00    1.00    1000199         2021-06-20 21:13:07 475388  1000199 2021-06-30
1000227 180 180 ILCH    20  20210811    IM  57193ZZ 11411   TOP SHELF   0.00    0.00    66.50   66.50   -66.50  -66.50  0.0000  0.00    0.00    1000227         2021-08-11 21:12:25 501018  1000227 2021-08-11
1000227 180 180 ILCH    20  20210628    IM  57193ZZ 11411   TOP SHELF   3141.65 3141.65 2547.24 2547.24 594.41  594.41  0.1892  0.00    1.00    1000227         2021-06-28 21:11:40 479037  1000227 2021-08-11
1000233 177 177 KYLX    181 20210630    IM  11703ZZ 10051   JOHNS MANVILLE  657.00  657.00  -68.42  -68.42  725.42  725.42  1.1041  0.00    0.00    1000233         2021-06-30 21:11:02 481135  1000233 2021-10-22
1000233 177 177 KYLX    181 20210817    IM  11703ZZ 10051   JOHNS MANVILLE  -657.00 -657.00 0.00    0.00    -657.00 -657.00 1.0000  0.00    0.00    1000233         2021-08-17 21:12:19 503628  1000233 2021-10-22
1000233 177 177 KYLX    181 20210615    IM  11703ZZ 10051   JOHNS MANVILLE  3932.50 3932.50 4597.07 4597.07 -664.57 -664.57 -0.1690 0.00    1.00    1000233         2021-06-15 21:10:42 473461  1000233 2021-10-22

原因分析 #

• 子查询里的DISTINCT shipmentid, GPRecDate是对两个字段的组合去重，不是单独对shipmentid去重。如果同一个shipmentid对应不同的GPRecDate，会被当成不同的记录保留，和主表关联后就会产生重复的shipmentid结果。
• UNION本身会自动对合并后的结果去重，但同样是基于整个行的字段组合，不是只针对shipmentid。

解决思路 #

方案1：子查询仅提取唯一的shipmentid #

如果不需要GPRecDate字段，或者可以忽略该字段的差异，直接在子查询里只取唯一的shipmentid即可：

FROM
    cs.acct_sales_daily_extract2 sa
    inner JOIN
        ( 
               select shipmentid
                from custom_cs.apj 
                where apdelay > 0 
                                                        
                union 
                
                select shipmentid
                from custom_cs.arh 
                where ardelay > 0 
        ) shd on sa.shipment_id = shd.shipmentid

注：UNION会自动对合并后的结果去重，因此可以省略内部的DISTINCT，简化语句。

方案2：对同一shipmentid保留指定的GPRecDate #

如果需要保留GPRecDate字段，需明确对同一个shipmentid要保留哪个日期（比如最新、最早），用聚合函数处理：

FROM
    cs.acct_sales_daily_extract2 sa
    inner JOIN
        ( 
               select shipmentid, MAX(GPRecDate) as GPRecDate -- 取最新日期，替换为MIN可获取最早日期
                from (
                    select shipmentid, GPRecDate
                    from custom_cs.apj 
                    where apdelay > 0 
                                                    
                    union 
                    
                    select shipmentid, GPRecDate
                    from custom_cs.arh 
                    where ardelay > 0 
                ) t
                group by shipmentid
        ) shd on sa.shipment_id = shd.shipmentid

方案3：主查询层面去重 #

如果主表本身存在重复的shipmentid记录，或关联后产生重复，可以在主查询结果上添加DISTINCT（不推荐在大表使用，会影响查询性能）：

SELECT DISTINCT sa.* -- 或指定你需要的具体字段
FROM
    cs.acct_sales_daily_extract2 sa
    inner JOIN
        ( 
               select distinct shipmentid, GPRecDate 
                from custom_cs.apj 
                where apdelay > 0 
                                                        
                union 
                
                select distinct shipmentid, GPRecDate 
                from custom_cs.arh 
                where ardelay > 0 
        ) shd on sa.shipment_id = shd.shipmentid

内容的提问来源于stack exchange，提问作者user3465081