如何计算SQL Server中超级集中设置的现有值的数量

I have data like :

我有以下数据:

select 'a,b,d' as set, 'a,b,c,e,f' as superset
union
select 'a,h, as set, 'a,b,c,d,e' as superset

Need output as :

需要输出为:

select 2 as existing_in_set, 1 as 'new'
union
select 1 as existing_in_set, 1 as 'new'

5 个解决方案

#1

xquery (XML): count - count(distinct-values())

xquery(XML):count - count(distinct-values())

create table #mytable ([set] varchar(100),[superset] varchar(100))
insert into #mytable ([set],[superset]) values ('a,b,d','a,b,c,e,f'),('a,h','a,b,c,d,e')

select  [set],[superset]    

       ,  x.value('count(                /r/e[text()!=""])' ,'int') 
        - x.value('count(distinct-values(/r/e[text()!=""]))','int') as common_elements  

from    (select  [set],[superset]   

                ,cast
                 (
                     '<r><e>'+replace([set]+','+[superset],',','</e><e>')+'</e></r>' 
                      as xml
                 ) as x

         from    #mytable
         ) t

+-------+-----------+-----------------+
| set   | superset  | common_elements |
+-------+-----------+-----------------+
| a,b,d | a,b,c,e,f | 2               |
+-------+-----------+-----------------+
| a,h   | a,b,c,d,e | 1               |
+-------+-----------+-----------------+

#2

Here is one way using Recursive CTE

这是使用递归CTE的一种方法

;WITH data
     AS (SELECT [set],
                superset,
                cs.Item,
                cs.ItemNumber
         FROM   (VALUES ('a,t,h', 'a,b,c,d,e,f' ),
                        ('a,h','a,b,c,d,e' )) tc ([set], superset)
                CROSS apply [Delimitedsplit8k]([set], ',') cs),
     cte
     AS (SELECT [set],
                superset,
                Item,
                Replace(',' + superset, + ',' + Item, '') AS result,
                ItemNumber
         FROM   data
         WHERE  ItemNumber = 1
         UNION ALL
         SELECT 
                d.[set],
                d.superset,
                d.Item,
                CASE
                  WHEN LEFT(Replace(',' + result, ',' + d.Item, ''), 1) = ',' THEN Stuff(Replace(',' + result, ',' + d.Item, ''), 1, 1, '')
                  ELSE Replace(',' + result, ',' + d.Item, '')
                END,
                d.ItemNumber
         FROM   cte c
                JOIN data d
                  ON c.superset = d.superset
                     AND d.ItemNumber = c.ItemNumber + 1)
SELECT TOP 1 WITH ties [set],superset,
                      (len(superset) -  len(Isnull(Stuff(result, 1, 1, ''), '')))/2 as Existing_in_set,
                      len(replace([set],',','')) -  ((len(superset) -  len(Isnull(Stuff(result, 1, 1, ''), '')))/2) as New
FROM   cte
ORDER  BY Row_number()OVER(partition BY superset ORDER BY ItemNumber DESC)

Referred from my old answer

从我的旧答案中提到

SQL Server Remove some specific characters from string

SQL Server从字符串中删除一些特定字符

Split string Function code referred from http://www.sqlservercentral.com/articles/Tally+Table/72993/

拆分字符串功能代码参考http://www.sqlservercentral.com/articles/Tally+Table/72993/

#3

Referring this blog, it is possible to get the required output using this query:

参考此博客,可以使用此查询获取所需的输出:

WITH    CTE
      AS ( SELECT   1 ID ,
                    'a,b,d' AS [set] ,
                    'a,b,c,e,f' AS superset
           UNION
           SELECT   2 ID ,
                    'a,h' AS [set] ,
                    'a,b,c,d,e' AS superset
         )
SELECT  CTE.ID ,
        CTE.[set] ,
        CTE.[superset] ,
        SUM(IIF(CHARINDEX(x.SingleSet, x.superset) > 0, 1, 0)) existing_in_set ,
        SUM(IIF(CHARINDEX(x.SingleSet, x.superset) = 0, 1, 0)) [new]
FROM    CTE
        JOIN ( SELECT   ID ,
                        LTRIM(RTRIM(m.n.value('.[1]', 'varchar(8000)'))) AS SingleSet ,
                        [superset]
               FROM     ( SELECT    ID ,
                                    [superset] ,
                                    CAST('<XMLRoot><RowData>'
                                    + REPLACE([set], ',',
                                              '</RowData><RowData>')
                                    + '</RowData></XMLRoot>' AS XML) AS x
                          FROM      CTE
                        ) t
                        CROSS APPLY x.nodes('/XMLRoot/RowData') m ( n )
             ) x ON CTE.ID = x.ID
GROUP BY CTE.ID ,
        CTE.[set] ,
        CTE.[superset];

And it's result looks like:

它的结果如下:

#4

*****FUNCTION [F_FindStringInString]*****

    CREATE FUNCTION [F_FindStringInString]
( 
    @FindString Varchar(300),
    @String Varchar(200)
) 
RETURNS
    @returnList TABLE (existing_in_set int, New int)
AS 
Begin
    Declare  @String_Temp varchar(20), @Char varchar(20), @existing_in_set int, @New int
    Set @existing_in_set = 0
    Set @New = 0 
    Set @String_Temp = @FindString
    While(LEN(@String_Temp)>1) and (CHARINDEX(',',@String_Temp))>0
    Begin
        Set @Char = SUBSTRING(@String_Temp,1,CHARINDEX(',',@String_Temp))
        Select @Char = LTRIM(@Char)
        If (Substring(@Char,1,CHARINDEX(',',@Char)-1) = Substring(REVERSE(@String),1,CHARINDEX(',',REVERSE(@String))-1))
            Or (LEN(@String) - LEN(REPLACE(@String, @Char, '')))/LEN(@Char) >0
        Begin
            Set @existing_in_set  = @existing_in_set + 1
        End
        Else
            Set @New = @New + 1
        Set @String_Temp = REPLACE(@String_Temp,SUBSTRING(@String_Temp,1,CHARINDEX(',',@String_Temp)),'')
    End

    If (LTRIM(@String_Temp) = REVERSE(Substring(REVERSE(@String),1,CHARINDEX(',',REVERSE(@String))-1)))
        Or (LEN(@String) - LEN(REPLACE(@String, LTRIM(@String_Temp), '')))/LEN(LTRIM(@String_Temp)) >0
    Begin
        Set @existing_in_set  = @existing_in_set + 1
    End
    Else
        Set @New = @New + 1

    INSERT INTO @returnList
    SELECT Isnull(@existing_in_set,0),Isnull(@New,0)

    Return
End

Declare @table Table ([Set] varchar(20), superset varchar(20))
Insert into @table ([Set], superset)
select 'a,b,d' as [set], 'a,b,c,e,f' as superset
union
select 'a,h' as [set], 'a,b,c,d,e' as superset

Select      D1.[Set], D1.superset,
            D2.existing_in_set , D2.New
From        @table D1
CROSS apply dbo.F_FindStringInString (D1.[Set], D1.superset) D2

#5

If you use SQL Server 2016 look function "STRING_SPLIT" https://msdn.microsoft.com/ru-ru/library/mt684588.aspx

如果您使用SQL Server 2016外观功能“STRING_SPLIT”https://msdn.microsoft.com/ru-ru/library/mt684588.aspx

#1