是否有一种有效的方法来匹配具有相同详细信息集的倍数标头？

Question

下面是我要问的一个例子：

明细表：

HeaderId | DetailId
    1          100
    1          101
    2          100
    2          101
    3          101
    3          102
    3          103

我正在寻找一种查询策略，它将为我获得所有头ids，并为每个头ids设置相同的详细ids。

所以在这个例子中，我希望HeaderId 1和2连接在一起，因为它们有相同的两条详细记录，但是103不匹配，因为它在集合中有第三项。

到目前为止，我采取的策略是使用STUFF技术创建一个逗号单独的详细值字符串，校验该字符串，然后加入校验和结果。它似乎在工作，但我不知道如何优化它。在一组大约7000头的标题中，它大约在6-7秒内返回.

以下是查询：

with Details as 
(   
    select distinct t2.HeaderId, 
    checksum(stuff((
        select
            ',' + convert(varchar(15), t2.DetailId)
        from
            DetailTable t2
        where
            t2.HeaderId = t2.HeaderId
        for xml path('')
    ),1,1,'')) as ChkSum
from
      DetailTable t1
)
select
    *
from
    Details t1
        join Details t2
            on t2.ChkSum = t1.ChkSum
            and t2.HeaderId <> t1.HeaderId -- To avoid matching the same record

所以-这是正确的方法吗？如果是的话，我如何优化呢？查询计划对我没有任何影响。最重的是一个桌子的卷轴。而且，如果这有帮助的话，我会尝试把它变成一个函数或proc。

编辑:我开始研究关系划分，我认为这在这里是相关的，但可能与我所想的上下文无关。为了提供更多的上下文，下面是我试图解决的业务案例。

我有一套推广，可以有任何数量的UPC在他们。我正在努力寻找有完全相同的UPC在他们的促销。我看到的许多解决方案都依赖于使用count(*)。所以.只是给任何人看这个的背景。谢谢!

Answer 1

因此，尼尔·麦克吉根关于关系部门的评论让我转向了这篇文章。

我发现“托德的部门-Dwain.C1”的例子是表演性的，并给出了我想要的结果。

这是本文中的示例，除了字段名/表名外，我几乎逐字使用该示例：

-- Todd's Division - Dwain.C 1
SELECT j.ProjectID, s.ResourceID
FROM #ProjectTasks j
JOIN #ResourceTasks s ON j.TaskID = s.TaskID
JOIN
(
    SELECT ProjectID, c_res=COUNT(*)
    FROM #ProjectTasks
    GROUP BY ProjectID
) c ON j.ProjectID = c.ProjectID
GROUP BY j.ProjectID, ResourceID
HAVING COUNT(*) = MAX(c_res)
ORDER BY j.ProjectID, ResourceID;

谢谢你提出的其他建议，他们没能让我完全理解，但最终我找到了解决方案。

Answer 2

下面是一种使用PIVOT和that的方法，如果您有<255个唯一的DetailIDs，就可以工作。在编写完CONCAT函数(2012+)并对其进行压力测试之后，我遇到了一些限制。它运行得很好，在40k行的20k头上<5秒，有254个唯一的详细键和大量匹配。如果你的套装符合这个限制，也许值得一看。

DECLARE @sql varchar(MAX)
DECLARE @d varchar(MAX)
SET @d = stuff((
        SELECT ',' + QUOTENAME(DetailId)
        FROM (SELECT DetailId FROM DetailTable GROUP BY DetailId) d
        for xml path('')
    ),1,1,'')
DECLARE @tbl TABLE (H int, D varchar(254))
INSERT INTO @tbl
EXEC(
'SELECT HeaderId,CONCAT('+@d+') Details --'+@d+'
FROM 
(
    SELECT HeaderId, DetailId, 1 o
    FROM DetailTable
) as s
PIVOT
(
    COUNT(o)
    FOR DetailId IN ('+@d+')
) as pvt'
--Possible subquery and JOIN?
)

SELECT t1.H, t2.H H2  --matches
FROM @tbl t1 JOIN @tbl t2 ON t1.D = t2.D AND t1.H < t2.H

您应该能够绕过CONCAT 254的限制，方法是用@d替换select，将枢轴封装到子查询中，并为联接填充另一个@dj。

Answer 3

您可以尝试使用checksum_agg而不是xml连接。

with c as (
  select
    h = headerid,
    g = checksum_agg(d) 
           over(partition by headerid)
  from detailtable
)

select distinct main, copy
from (
select
    main = min(l.h) over(partition by l.g), 
    copy = l.h
from c as l
) x
where x.main < x.copy

小提琴：http://sqlfiddle.com/#!6/df56a/16