如何在SQL查询中去除quantity与created字段值重复的记录？

问题背景 #

你执行以下SQL查询后，结果表存在quantity和created字段值完全相同的重复记录，需要去除这些重复项：

SELECT quantity, transferred, created
FROM ship
WHERE transferred = 0 
  AND resp IN ('6982347', '6982347') 
  AND state LIKE '%FA84%' OR state LIKE '%0F6D%' OR state LIKE '%H438%'

你尝试的CTE语句未生效，原因是原CTE仅针对created字段统计唯一值，既没结合quantity字段做重复判断，也没正确关联主查询的逻辑：

WITH DuplicateValue AS 
(
    SELECT 
        created, COUNT(*) AS CNT
    FROM 
        ship 
    GROUP BY 
        created
    HAVING COUNT(*) = 1
 )

后续添加created IN (SELECT created FROM DuplicateValue)也无法解决问题，因为根本没覆盖quantity字段的重复判断逻辑。

解决方案 #

方法1：用窗口函数精准去重（推荐） #

通过ROW_NUMBER()窗口函数按quantity和created分组编号，只保留每组的第一条记录，同时修正原查询中WHERE条件的逻辑优先级问题（OR需要和前面的条件用括号分隔，否则逻辑会偏离预期）：

WITH RankedRecords AS (
    SELECT 
        quantity, transferred, created,
        ROW_NUMBER() OVER (PARTITION BY quantity, created ORDER BY (SELECT NULL)) AS rn
    FROM ship
    WHERE transferred = 0 
      AND resp IN ('6982347', '6982347') 
      AND (state LIKE '%FA84%' OR state LIKE '%0F6D%' OR state LIKE '%H438%')
)
SELECT quantity, transferred, created
FROM RankedRecords
WHERE rn = 1;

• 说明：PARTITION BY quantity, created表示按这两个字段分组，每组内的记录会被依次编号；ORDER BY (SELECT NULL)表示不指定排序规则，随机保留一条，若需要按特定规则保留（比如最新的记录），可以改成ORDER BY created DESC。

方法2：用DISTINCT快速去重（适合简单场景） #

如果你的查询只需要保留quantity、transferred、created这三个字段的唯一组合，直接在SELECT后加DISTINCT即可，写法更简洁：

SELECT DISTINCT quantity, transferred, created
FROM ship
WHERE transferred = 0 
  AND resp IN ('6982347', '6982347') 
  AND (state LIKE '%FA84%' OR state LIKE '%0F6D%' OR state LIKE '%H438%');

• 注意：如果表中还有其他字段需要保留，这种方法不适用，此时优先选择窗口函数方案。

方法3：修正你原有的CTE写法 #

调整CTE，同时按quantity和created分组，找出所有唯一的字段组合，再关联主查询获取完整记录：

WITH UniquePairs AS (
    SELECT quantity, created
    FROM ship
    GROUP BY quantity, created
    -- 若仅需保留仅出现一次的组合，可添加：HAVING COUNT(*) = 1
)
SELECT s.quantity, s.transferred, s.created
FROM ship s
JOIN UniquePairs up ON s.quantity = up.quantity AND s.created = up.created
WHERE s.transferred = 0 
  AND s.resp IN ('6982347', '6982347') 
  AND (s.state LIKE '%FA84%' OR s.state LIKE '%0F6D%' OR s.state LIKE '%H438%');

• 说明：这种写法适合需要对重复组合做额外处理的场景，但相比窗口函数，写法更繁琐。

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