获取在逗号分离格式SQL中具有相同值的列计数

Question

嗨，我需要一个复杂的查询我的表结构是

attribute_id value    entity_id 
188         48,51,94     1
188         43,22        2
188         43,22        3
188         43,22        6
190         33,11        10
190         90,61        12
190         90,61        15

我需要像

的值计数

attribute_id value    count
188         48         2
188         43         3
188         51         1
188         94         1
188         22         2
190         33         1
190         11         1
190         90         2
190         61         2

我已经在Google上搜索了很多东西，但是不幸的是我没有取得任何成功。请建议我如何实现此目标。

Answer 1

我将UDF用于类似的事情。如果那可以对您有用：

CREATE FUNCTION [dbo].[UDF_StringDelimiter]
/*********************************************************
**  Takes Parameter "LIST" and transforms it for use    **
**  to select individual values or ranges of values.    **
**                                                      **
**  EX: 'This,is,a,test' = 'This' 'Is' 'A' 'Test'       **
*********************************************************/
    (
          @LIST             VARCHAR(8000)
         ,@DELIMITER        VARCHAR(255)
    )
RETURNS @TABLE TABLE 
    ( 
         [RowID] INT IDENTITY
        ,[Value] VARCHAR(255)
    )
WITH SCHEMABINDING
AS 
BEGIN
    DECLARE 
         @LISTLENGTH AS SMALLINT
        ,@LISTCURSOR AS SMALLINT
        ,@VALUE AS VARCHAR(255)
    ;
    SELECT 
         @LISTLENGTH = LEN(@LIST) - LEN(REPLACE(@LIST,@DELIMITER,'')) + 1
        ,@LISTCURSOR = 1
        ,@VALUE = ''
    ;
    WHILE @LISTCURSOR <= @LISTLENGTH
    BEGIN
        INSERT INTO @TABLE (Value)
        SELECT 
            CASE
                WHEN @LISTCURSOR < @LISTLENGTH
                    THEN SUBSTRING(@LIST,1,PATINDEX('%' + @DELIMITER + '%',@LIST) - 1)
                ELSE SUBSTRING(@LIST,1,LEN(@LIST))
            END
        ;
        SET @LIST = STUFF(@LIST,1,PATINDEX('%' + @DELIMITER + '%',@LIST),'')
        ;       
        SET @LISTCURSOR = @LISTCURSOR + 1
        ;
    END
    ;
    RETURN
    ;
END
;

UDF采用两个参数：要拆分的字符串，以及要分开的定界符。这些年来，我一直在为各种不同的事物使用它，因为有时您需要用逗号分开，有时是由空间分开，有时是由整个字符串分开的。

拥有该UDF后，您只需执行此操作：

DECLARE @TABLE TABLE 
(
     Attribute_ID INT
    ,Value VARCHAR(55)
    ,Entity_ID INT
);
INSERT INTO @TABLE VALUES (188, '48,51,94', 1);
INSERT INTO @TABLE VALUES (188, '43,22', 2);
INSERT INTO @TABLE VALUES (188, '43,22', 3);
INSERT INTO @TABLE VALUES (188, '43,22', 6);
INSERT INTO @TABLE VALUES (190, '33,11', 10);
INSERT INTO @TABLE VALUES (190, '90,61', 12);
INSERT INTO @TABLE VALUES (190, '90,61', 15);
SELECT
     T1.Attribute_ID
    ,T2.Value
    ,COUNT(T2.Value) AS Counter
FROM @TABLE T1
CROSS APPLY dbo.UDF_StringDelimiter(T1.Value,',') T2
GROUP BY T1.Attribute_ID,T2.Value
ORDER BY T1.Attribute_ID ASC, Counter DESC
;

我做了一个 ORDER BY attribute_id上升，然后进行计数器下降，以便您首先获得具有最常见的重复值的每个属性。当然，您可以更改。

返回以下内容：

Attribute_ID     Value     Counter
-----------------------------------
188              43            3
188              22            3
188              94            1
188              48            1
188              51            1
190              61            2
190              90            2
190              11            1
190              33            1