在MS SQL Server 2008中排除“部分”重复行

Question

-- 如何在MS SQL Server 2008中实现此功能？有何建议？

-- 我所指的“重复行”是指“前3个字段匹配” -- 我需要从结果中排除重复项，但我需要决定保留哪一行 -- 保留第4列中有值的行（每组重复的数据对只会有1行）

-- 结果应保留第2行（但不保留第1行） -- 同样保留第3行（但不保留第4行） -- 保留所有其余行（没有重复行） -- 应返回10行中的8行

IF(OBJECT_ID('tempdb..#tmp') IS NOT NULL)       DROP TABLE #tmp

CREATE TABLE #tmp
(
    aKey Int            IDENTITY(1,1)   PRIMARY KEY,    
    
    f1 VarChar(10)          NOT NULL    DEFAULT 0,  -- 1
    f2 VarChar(10)          NOT NULL    DEFAULT 0,  -- 2
    f3 VarChar(10)          NOT NULL    DEFAULT 0,  -- 3 
    f4 VarChar(10)          NOT NULL    DEFAULT 0,
    f5 VarChar(10)          NOT NULL    DEFAULT 0,
    f6 VarChar(10)          NOT NULL    DEFAULT 0
)

INSERT INTO #tmp(f1, f2, f3, f4, f5, f6)    VALUES('A', 'B', 'C', '' , 'del', '1') -- 1st of the duplicate
INSERT INTO #tmp(f1, f2, f3, f4, f5, f6)    VALUES('A', 'B', 'C', 'D', 'keep', '1') -- 2nd of the duplicate

INSERT INTO #tmp(f1, f2, f3, f4, f5, f6)    VALUES('D', 'E', 'F', 'G', 'keep', '2') -- 1st of the duplicate
INSERT INTO #tmp(f1, f2, f3, f4, f5, f6)    VALUES('D', 'E', 'F', '' , 'del', '2') -- 2nd of the duplicate

INSERT INTO #tmp(f1, f2, f3, f4, f5, f6)    VALUES('H', 'G', 'N', 'Q', '1', 'K')  
INSERT INTO #tmp(f1, f2, f3, f4, f5, f6)    VALUES('I', 'G', 'C', '' , '2', 'L')  
INSERT INTO #tmp(f1, f2, f3, f4, f5, f6)    VALUES('J', 'H', 'D', 'R', '3', 'P')  
INSERT INTO #tmp(f1, f2, f3, f4, f5, f6)    VALUES('K', 'G', 'C', '' , '4', 'K')  
INSERT INTO #tmp(f1, f2, f3, f4, f5, f6)    VALUES('L', 'G', 'C', 'S', '5', 'V')  
INSERT INTO #tmp(f1, f2, f3, f4, f5, f6)    VALUES('K', 'M', 'C', '' , '6', 'K')  

--从#tmp表中选择所有数据

-- This "almost" works, but is excluding too many non-duplicate rows:

SELECT DISTINCT t1.* FROM #tmp AS t1
INNER JOIN #tmp AS t2
ON t1.f1 = t2.f1 AND t1.f2 = t2.f2 AND t1.f3 = t2.f3
WHERE t1.f4 <> ''

- EllenEEE

---

如果第4列有两条记录，但前3列匹配，会发生什么？实质上，您想要使用 row_number() over (partition by f1,f2,f3, f4 desc) 并且仅返回第1行。这将为每个“相似”的重复项分配行号，然后仅返回第1行。但是，考虑到您的条件只返回具有 f4 值的记录......在确定要使用哪条记录方面存在太多不确定性。 - xQbert

Answer 1

首先，非常棒的工作，以人们可以使用的格式发布示例数据。太好了！这使得人们更容易提供帮助。其次，尽管您的说明并不完全清晰，但在示例数据中提供了足够的细节，使得需要完成的任务变得清晰明了。xQbert提到使用ROW_NUMBER是解决此问题的好方法。

我在这里使用了cte来提供一些清晰度。如果您愿意，您也可以轻松地将其作为子查询执行。

with SortedValues as
(
    select *
        , RowNum = ROW_NUMBER() over (partition by f1, f2, f3 order by f4 desc)
    from #tmp
)

select *
from SortedValues
where RowNum = 1
order by aKey

- Sean Lange

---

最好也解释一下空字符串 '' 和 NULL 会在任何其他内容之前排序（如果排序是 DESC，则在其后）。 - Charlieface

@Charlieface，非常好的观点。 - Sean Lange

我发现“嵌套方法”更容易理解。但是当我尝试使用“with/as/over/partition”方法（这是我第一次使用它）时，它也同样有效。太棒了！而且奖励是，我现在可以在将来的项目中使用“with/as/over/partition”，因为它并不难理解。 - EllenEEE

Answer 2

按照您现有的方法，不要使用join，而是使用exists

select * 
from t
where not exists (
    select * from t t2 
    where t.f1=t2.f1 
        and t.f2=t2.f2 
        and t.f3=t2.f3 
        and t.f4 =''
        and t2.akey !=t.akey
    )

- Stu

---

这不太可能像行编号方法那样高效。 - Charlieface

这将会对表格有更多的逻辑读取，行号将需要对行进行排序；在真实世界的数据测试中，显然会显示哪个更好。 - Stu

不一定是这样的：exists 可能会被转换成排序合并连接。而且 @SeanLange 查询中的最终 order by 实际上并不是必要的。我承认，如果有很大比例的重复行，您的解决方案可能更快，但这是不寻常的。同意需要在真实数据上进行测试。 - Charlieface

@Charlieface 实际上，如果没有最终的 order by 子句，就无法确定输出行的顺序。如果顺序不重要，那么你是正确的。 - Sean Lange