主页 / dba / 问题 / 195004
Accepted
IronicMuffin
Asked: 2018-01-10 10:53:26 +0800 CST

有没有一种有效的方法来匹配具有相同详细信息集的多个标头？

  • 772

这是我要问的一个例子：

明细表：

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

我正在寻找一种查询策略，该策略将使我获得所有标头 ID，每个标头 ID 都具有相同的详细信息集。

所以在这个例子中，我希望 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

那么 - 这是正确的方法吗？如果是，我该如何优化？查询计划对我没有任何影响。最大重量分配给工作台线轴。此外，如果有帮助，我正在尝试将其设为函数或过程。

编辑：我开始研究关系划分，我认为这在这里是相关的，但也许与我正在考虑的上下文无关。为了提供更多背景信息，这是我试图解决的业务案例。

我有一组促销活动，其中可以包含任意数量的 UPC。我试图找到其中具有完全相同的一组 UPC 的促销活动。我看到的很多解决方案都依赖于使用count(*). 所以 - 只是为任何正在看这个的人提供一些背景信息。谢谢！

sql-server t-sql

4 个回答

  1. 这是一种使用PIVOT和 T-SQL 的方法，如果你有 < 255 unique 就可以工作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通过仅将选择替换为 @d、将 PIVOT 包装到子查询中并为 JOIN 填充另一个 @dj 来绕过 254 限制。

    • 2

  2. 您可以尝试使用 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

    • 2 
  3. with cte1 as 
select headerID, count(*) as cnt 
from Detail 
group by headerID, 

cte2 as 
select h1.headerID as ID1, h2.headerID as ID2, count(*) as cnt 
from detail h1 
join detail h2 
 on h1.headerID < h2.headerID 
and h1.DetailId = h2.DetailId 
group by h1.headerID, h2.headerID

select cte2.* 
from cte2 
join cte1 as cte1a
  on cte1a.headerID = cte2.ID1  
 and cte1a.cnt      = cte2.cnt 
join cte1 as cte1b
  on cte1b.headerID = cte2.ID2  
 and cte1b.cnt      = cte2.cnt

    我敢打赌，完全外部联接并查找 null 会更简单，但我还没有测试过。我认为这也会降低效率。

    • 2
  4. Best Answer

    所以@Neil McGuigan 关于关系划分的评论让我看到了这篇文章。

    我发现“Todd's Division - Dwain.C 1”的示例性能良好，并为我提供了我正在寻找的结果。

    这是文章中的示例，除了字段/表名称外，我几乎一字不差地使用：

    -- 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;

    感谢您提出的其余建议，他们并没有完全让我到达那里，但最终我找到了解决方案。

    • 2

相关问题