我有一个订单表,我需要找到重复的订单-具有相同的dd_编号和dd_代码的订单,但其中一个订单必须具有“checkout_completed”状态。
以下是我当前的SQL,它正确地查找具有相同dd U编号和dd U代码的重复订单,但是我无法确定如何检查状态:
SELECT
tbOrders.id, tbOrders.dd_number, tbOrders.dd_code, tbOrders.status, COUNT(*) AS totalDupes
FROM
tbOrders
WHERE
dd_number IS NOT NULL
AND
dd_number != ''
AND
dd_number != '000000'
AND
dd_number != '00000000'
GROUP BY
dd_number, dd_code
HAVING totalDupes > 1
添加状态为“checkout_complete”的WHERE子句不起作用,因为它要求两个订单具有相同的状态。任何帮助都将不胜感激!
最佳答案
SELECT
tbOrders.id, tbOrders.dd_number, tbOrders.dd_code, tbOrders.status, COUNT(*) AS totalDupes,
SUM(CASE WHEN status='checkout_complete' THEN 1 ELSE 0 END) as status_count
FROM
tbOrders
WHERE
dd_number IS NOT NULL
AND
dd_number != ''
AND
dd_number != '000000'
AND
dd_number != '00000000'
GROUP BY
dd_number, dd_code
HAVING totalDupes > 1 and status_count=1
您可以再引入一个具有以下状态的聚合字段计数记录