需要列出类似的子组

Question

请帮我把类似团体的名单放在一起。

请看上面的插图。

在这里，BaseItem 6586、6587、6588、6589正在共享类似的SubItems (SubSet 1、SubSet2、SubSet3和SubSet4 )。

此外，BaseItem 14和80共享类似的SubItems(SubSet 5和SubSet 8)。

同时，BaseItem 7000,7010没有共享类似的SubItems，因为它们的子集有更多或更少的SubItems。

因此，我需要的是识别和分组相似的子集，并有一个独特的标识。

请参见下面带有数据的脚本。

CREATE TABLE Table1 ([BaseItem] int, [SubItem] int, primary key (BaseItem, SubItem)) ;

INSERT INTO Table1
    ([BaseItem], [SubItem])
VALUES
    (6586, 3913),
    (6586, 7460),
    (6586, 15659),
    (6587, 3913),
    (6587, 7460),
    (6587, 15659),
    (6588, 3913),
    (6588, 7460),
    (6588, 15659),
    (6589, 3913),
    (6589, 7460),
    (6589, 15659),
    (14, 2459),
    (14, 2460),
    (7000, 3913),
    (7000, 7460),
    (7000, 15659),
    (7000, 1256),
    (7010, 3913),
    (7010, 7460),
    (80, 2459),
    (80, 2460),
    (200, 2459),
    (200, 2460),
    (200, 2870),
    (200, 1951);

为了更清楚，请看所有的细节。

我有两张桌子，

Equipments
-----------------
Printer a
Printer b
Printer c
Printer d
Printer e
Printer f
Printer g

Accessories
-------------
Accessory a
Accessory b
Accessory c
Accessory d
Accessory e
Accessory f

为了显示每个打印机的兼容附件，我组合了两个表并生成了另一个视图。报告将提供以下结果：

Equipments       |    Accessories   
--------------------------------------------
Printer a             Accessory b
Printer a             Accessory c
Printer a             Accessory e

Printer b             Accessory a
Printer b             Accessory b

Printer c             Accessory b
Printer c             Accessory c

Printer d             Accessory a
Printer d             Accessory b

Printer e             Accessory b
Printer e             Accessory c
Printer e             Accessory e

Printer f             Accessory d
Printer f             Accessory e

Printer g             Accessory a
Printer g             Accessory b

最终结果必须是：

ID  |     Equipments       |    Accessories   
--------------------------------------------
1         Printer a             Accessory b
1         Printer a             Accessory c
1         Printer a             Accessory e

1         Printer e             Accessory b
1         Printer e             Accessory c
1         Printer e             Accessory e

2         Printer b             Accessory a
2         Printer b             Accessory b

2         Printer d             Accessory a
2         Printer d             Accessory b

2         Printer g             Accessory a
2         Printer g             Accessory b

3         Printer c             Accessory b
3         Printer c             Accessory c

4         Printer f             Accessory d
4         Printer f             Accessory e

Answer 1

编辑后的版本，这个版本将只获取至少与另一个组匹配的组(=所有相同的项)，这假设组中的项是唯一的。

select *
from Table1 T1
outer apply (
  select count(*) as C from Table1 T2 where T1.BaseItem = T2.BaseItem
) C1
outer apply (
  select top 1
  row_number() over (partition by T3.BaseItem, T4.BaseItem order by T3.SubItem) as RN,
  T4.BaseItem
  from Table1 T3 
  join Table1 T4 on T3.BaseItem != T4.BaseItem
  and T3.SubItem = T4.SubItem
  and T3.BaseItem = T1.BaseItem
  order by RN desc
) C2
outer apply (
  select count(*) as C from Table1 T5 where T5.BaseItem = C2.BaseItem
) C3
where C1.C = C2.RN AND
C1.C = C3.C

SQL Fiddle中的示例