mysql - 删除 UNION 结果 Mysql 中的重复项

Question

我有一些表，我使用 UNION 创建查询以将这些表连接到一个表。但我有一些问题，结果有重复的值。

这是我的 UNION 的结果:

这是我的查询:

SELECT * FROM (
SELECT d.type, d.is_delete, a.CUSTOMER_ID, a.ANI, a.FULL_NAME, a.ALAMAT, 
a.CITY, a.PROVINCE_DESC, a.ZIP_CODE, a.PSTN, a.MOBILE, a.EMAIL, 
b.location_id, c.INSTALL_ADDR, c.PSTN as UPD_PSTN, c.MOBILE as UPD_MOBILE 
FROM dailybussolbackup.daily_bussol_20150101 a 
LEFT JOIN collectionticket.ticket_location_kcp b ON a.CUSTOMER_ID = b.CUSTOMER_ID 
LEFT JOIN collectionticket.ticket_customers c ON a.CUSTOMER_ID = c.CUSTOMER_ID 
LEFT JOIN ticket_schedule_complaint d ON d.customer_id = a.CUSTOMER_ID
WHERE b.location_id = '3'
      UNION
SELECT d.type, d.is_delete, a.CUSTOMER_ID, a.ANI, a.FULL_NAME, a.ALAMAT, 
a.CITY, a.PROVINCE_DESC, a.ZIP_CODE, a.PSTN, a.MOBILE, a.EMAIL, 
b.location_id, c.INSTALL_ADDR, c.PSTN as UPD_PSTN, c.MOBILE as UPD_MOBILE 
FROM dailybussolbackup.daily_bussol_20150101 a 
LEFT JOIN collectionticket.ticket_location_kcp b ON a.CUSTOMER_ID = b.CUSTOMER_ID 
LEFT JOIN collectionticket.ticket_customers c ON a.CUSTOMER_ID = c.CUSTOMER_ID 
LEFT JOIN ticket_schedule_detail d ON d.cust_id = a.CUSTOMER_ID
LEFT JOIN ticket_schedule e ON e.id = d.schedule_id
WHERE b.location_id = '3'
) as data
WHERE data.is_delete = 'false' OR data.is_delete IS NULL;

这是我的预期结果:

删除type和is_delete列具有NULL值且重复的行。怎么清理呢？？谢谢..

Answer 1

看起来其余字段就像结果集的“PK”。这将使用“type”和“is_delete”值(如果存在)，或者在其他情况下使用 null。
这有点棘手(可能是在子查询中分析更好的查询)

SELECT 
    MAX(type) as type, MAX(is_delete) as is_delete
    CUSTOMER_ID, ANI, FULL_NAME, ALAMAT, 
    CITY, PROVINCE_DESC, ZIP_CODE, PSTN, MOBILE, EMAIL, 
    location_id, INSTALL_ADDR, UPD_PSTN, UPD_MOBILE
FROM (
    SELECT d.type, d.is_delete, a.CUSTOMER_ID, a.ANI, a.FULL_NAME, a.ALAMAT, 
    a.CITY, a.PROVINCE_DESC, a.ZIP_CODE, a.PSTN, a.MOBILE, a.EMAIL, 
    b.location_id, c.INSTALL_ADDR, c.PSTN as UPD_PSTN, c.MOBILE as UPD_MOBILE 
    FROM dailybussolbackup.daily_bussol_20150101 a 
    LEFT JOIN collectionticket.ticket_location_kcp b ON a.CUSTOMER_ID = b.CUSTOMER_ID 
    LEFT JOIN collectionticket.ticket_customers c ON a.CUSTOMER_ID = c.CUSTOMER_ID 
    LEFT JOIN ticket_schedule_complaint d ON d.customer_id = a.CUSTOMER_ID
    WHERE b.location_id = '3'
          UNION
    SELECT d.type, d.is_delete, a.CUSTOMER_ID, a.ANI, a.FULL_NAME, a.ALAMAT, 
    a.CITY, a.PROVINCE_DESC, a.ZIP_CODE, a.PSTN, a.MOBILE, a.EMAIL, 
    b.location_id, c.INSTALL_ADDR, c.PSTN as UPD_PSTN, c.MOBILE as UPD_MOBILE 
    FROM dailybussolbackup.daily_bussol_20150101 a 
    LEFT JOIN collectionticket.ticket_location_kcp b ON a.CUSTOMER_ID = b.CUSTOMER_ID 
    LEFT JOIN collectionticket.ticket_customers c ON a.CUSTOMER_ID = c.CUSTOMER_ID 
    LEFT JOIN ticket_schedule_detail d ON d.cust_id = a.CUSTOMER_ID
    LEFT JOIN ticket_schedule e ON e.id = d.schedule_id
    WHERE b.location_id = '3'
) as data
GROUP BY
    CUSTOMER_ID, ANI, FULL_NAME, ALAMAT, 
    CITY, PROVINCE_DESC, ZIP_CODE, PSTN, MOBILE, EMAIL, 
    location_id, INSTALL_ADDR, UPD_PSTN, UPD_MOBILE
WHERE is_delete = 'false' OR is_delete IS NULL;