sql - 识别 SQL 中的重复数据组

Question

我的问题是如何识别 SQL 表中重复(重复)的数据“组”。我目前正在使用 SQL Server 2005，所以更喜欢基于它或 ansi-sql 的解决方案。

这是一个示例表和预期结果(如下)，以解决此问题:

declare @data table (id nvarchar(10), fund nvarchar(1), xtype nvarchar(1))

insert into @data select 'Switch_1', 'A', 'S'
insert into @data select 'Switch_1', 'X', 'B'
insert into @data select 'Switch_1', 'Y', 'B'
insert into @data select 'Switch_1', 'Z', 'B'
insert into @data select 'Switch_2', 'A', 'S'
insert into @data select 'Switch_2', 'X', 'B'
insert into @data select 'Switch_2', 'Y', 'B'
insert into @data select 'Switch_2', 'Z', 'B'
insert into @data select 'Switch_3', 'C', 'S'
insert into @data select 'Switch_3', 'D', 'B'
insert into @data select 'Switch_4', 'C', 'S'
insert into @data select 'Switch_4', 'F', 'B'

(新数据)

insert into @data select 'Switch_5', 'A', 'S'
insert into @data select 'Switch_5', 'X', 'B'
insert into @data select 'Switch_5', 'Y', 'B'
insert into @data select 'Switch_5', 'Z', 'B'

---

--   id         fund xtype match
--   ---------- ---- ----- ---------
--   Switch_1   A    S     Match_1
--   Switch_1   X    B     Match_1
--   Switch_1   Y    B     Match_1
--   Switch_1   Z    B     Match_1
--   Switch_2   A    S     Match_1
--   Switch_2   X    B     Match_1
--   Switch_2   Y    B     Match_1
--   Switch_2   Z    B     Match_1
--   Switch_3   C    S     
--   Switch_3   D    B     
--   Switch_4   C    S     
--   Switch_4   F    B  

(新结果)

--   Switch_5   A    S     Match_1
--   Switch_5   X    B     Match_1
--   Switch_5   Y    B     Match_1
--   Switch_5   Z    B     Match_1

我只想在全部或全无的基础上进行匹配(即组中的所有记录都匹配另一组中的所有记录 - 而不是部分匹配)。可以使用任何匹配 ID(我在上面使用了 Match_1，但可以是数字等)

在此感谢您的帮助。

(编辑:我想我应该补充一点，每组可以有任意数量的行，而不仅仅是上面示例中显示的 2 行或 4 行 - 而且我也在努力避免游标)

(编辑 2:如果找到多个匹配项，我似乎遇到了问题。当找到多个匹配项时，提供的 SQL 的输出返回 Switch_1 的重复记录。我已相应地更新示例数据. 不确定 Lieven 是否仍在关注这个 - 我也在寻找解决方案，如果找到会发布在这里。)

Answer 1

执行流程如下

• q:使用 XML PATH 将一个 id 的所有资金和 xtypes 组合成一个字符串施工
• r:选择一个ROW_NUMBER以及匹配组的相应 ID
• 通过 LEFT JOINING @data 和 r 选择结果

SQL语句

;WITH q AS (
  SELECT  DISTINCT d.id
          , DuplicateData = STUFF((SELECT ', ' + fund  + xtype FROM @data WHERE id = d.id FOR XML PATH('')), 1, 2, '')
  FROM    @data d
) 
, r AS (
  SELECT  id1 = q1.id
          , id2 = q2.id
          , rn = ROW_NUMBER() OVER (ORDER BY q1.ID)
  FROM    q q1
          INNER JOIN q q2 ON q1.DuplicateData = q2.DuplicateData AND q1.id < q2.id
)
SELECT  id
        , fund
        , xtype
        , match = 'Match_' + CAST(r.rn AS VARCHAR(32))
FROM    @data d
        LEFT OUTER JOIN r ON d.id IN (r.id1, r.id2)

结果

id         fund xtype match
---------- ---- ----- --------------------------------------
Switch_1   A    S     Match_1
Switch_1   X    B     Match_1
Switch_1   Y    B     Match_1
Switch_1   Z    B     Match_1
Switch_2   A    S     Match_1
Switch_2   X    B     Match_1
Switch_2   Y    B     Match_1
Switch_2   Z    B     Match_1
Switch_3   C    S     NULL
Switch_3   D    B     NULL
Switch_4   C    S     NULL
Switch_4   F    B     NULL