sql - 在 SQL Server 中查找重复的行组

Question

我有一个包含 Material 信息的表格，其中一种 Material 具有从一种到多种成分。

该表如下所示:

material_id contstiuent_id constituent_wt_pct
   1             1              10.5
   1             2              89.5
   2             1              10.5
   2             5              15.5
   2             7              74
   3             1              10.5
   3             2              89.5

一般来说，我可以有不同的 Material ID具有相同成分( ID 和重量百分比)，但具有相同重量百分比的相同成分 id 可以在多种 Material 中。

我要找 Material ID具有完全相同的成分数量、相同的成分 ID 和相同的重量百分比(在 Material ID 1 和 3 的数据示例中)
有这样的输出会很棒:

ID Duplicate ID's
1 1,3
2 15,25
....

只是为了澄清这个问题:我有几千种 Material ，如果我只得到重复行的 id 对我没有帮助 - 我想看看是否有可能在同一行中获得重复 Material id 的组或字段。

Answer 1

在包含所有成分的 CTE 中构建一个 XML 字符串，并使用该字符串确定哪些 Material 是重复的。

SQL Fiddle

MS SQL Server 2008 架构设置 :

create table Materials
(
  material_id int, 
  constituent_id int, 
  constituent_wt_pct decimal(10, 2)
);


insert into Materials values
(1, 1, 10.5),
(1, 2, 89.5),
(2, 1, 10.5),
(2, 5, 15.5),
(2, 7, 74),
(3, 1, 10.5),
(3, 2, 89.5);

查询 1 :

with C as
(
  select M1.material_id,
        (
        select M2.constituent_id as I,
                M2.constituent_wt_pct as P
        from Materials as M2
        where M1.material_id = M2.material_id
        order by M2.constituent_id,
                 M2.material_id
        for xml path('')
        ) as constituents
  from Materials as M1
  group by M1.material_id
)
select row_number() over(order by 1/0) as ID,
       stuff((
       select ','+cast(C2.material_id as varchar(10))
       from C as C2
       where C1.constituents = C2.constituents
       for xml path('')
       ), 1, 1, '') as MaterialIDs
from C as C1
group by C1.constituents
having count(*) > 1

Results :

| ID | MATERIALIDS |
--------------------
|  1 |         1,3 |