如何在SQL中从给定的结果字符串中找到max组合

Question

这是输出。

ID     Stack    
-----------------------------------
123    307290,303665,307285  
123    307290,307285,303424,303665  
123    307290,307285,303800,303665  
123    307061,307290  

我只想要最后三行的输出。原因是在第一输出行堆栈列中，所有三个数字都在输出行2和3堆栈列中可用，因此我不需要输出行1。

但是输出行2,3,4是不同的，所以我想在结果中看到这些行。

我试过用row_number()和charindex来做这件事，但是我没有得到正确的结果。

谢谢。

Answer 1

所有的评论告诉你改变你的数据库的结构是正确的！您确实应该避免逗号分隔的值。这是打破1.NF，并将是一个痛苦的脖子永远。

第二个CTE的结果可能用于将所有数据转换为一个新的1:n相关结构。

像这样吗？

DECLARE @tbl TABLE(ID INT,Stack VARCHAR(100));
INSERT INTO @tbl VALUES
 (123,'307290,303665,307285')
,(123,'307290,307285,303424,303665')
,(123,'307290,307285,303800,303665')
,(123,'307061,307290');

WITH Splitted AS
(
    SELECT ID
          ,Stack
          ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowIndex
          ,CAST('<x>' + REPLACE(Stack,',','</x><x>') + '</x>' AS XML) Casted 
    FROM @tbl
)
,DerivedDistinctValues AS
(
    SELECT DISTINCT 
           ID
          ,Stack
          ,RowIndex
          ,StackNr.value('.','int') AS Nr
    FROM Splitted
    CROSS APPLY Casted.nodes('/x') AS A(StackNr)
)
SELECT ddv1.ID
      ,ddv1.Stack
FROM DerivedDistinctValues AS ddv1
FULL OUTER JOIN DerivedDistinctValues AS ddv2 ON ddv1.RowIndex<>ddv2.RowIndex
                                             AND ddv1.Nr=ddv2.Nr
WHERE ddv2.ID IS NULL
GROUP BY ddv1.ID,ddv1.Stack 

这将是缓慢的，特别是在较大的数据集。

一些解释：

第一个CTE将把CSV数字转换为<x>307290</x><x>303665</x>... (可以将其转换为XML )，这允许生成一个派生表，将所有数字作为行返回。这发生在第二个调用XQuery函数.nodes()的CTE中。

最后一个查询将执行一个完整的外部联接--每个连接。所有行，其中至少有一行没有对应的行，将被保留。

但我认为，这可能不适用于每一种情况(例如循环数据)。