代码之家 › 专栏 › 技术社区 › Peter Willis

如何查询创建集合分组

tsql sql-server-2005

Peter Willis · 技术社区 · 16 年前

我有一个普通的连接表,其中有两列是其他表的键。这些记录是映射到一系列资产的客户。

asset_map  
-----------

customer_id    asset_id
-----------------------
     1            1
     1            2
     2            1
     2            2
     3            1
     3            2
     3            3
     4            1
     4            2

大约有10种资产,每个客户都可以映射到这些资产的任意组合。我想要达到的目标是:

grouping    customer_id    asset_id
------------------------------------
    1            1            1
    1            1            2
    1            2            1
    1            2            2  
- - - - - - - - - - - - - - - - - - 
    2            3            3
    2            3            1
    2            3            2  
- - - - - - - - - - - - - - - - - - 
    1            4            1
    1            4            2

请注意,客户1、2和4属于同一组,因为它们映射到资产1和2。客户3不属于1、2和3中的分组。

2 回复 | 直到 16 年前

Quassnoi 16 年前

WITH    rows (customer_id, asset_id) AS
        (
        SELECT  1, 1
        UNION ALL
        SELECT  1, 2
        UNION ALL
        SELECT  2, 1
        UNION ALL
        SELECT  2, 2
        UNION ALL
        SELECT  3, 1
        UNION ALL
        SELECT  3, 2
        UNION ALL
        SELECT  3, 3
        UNION ALL
        SELECT  4, 1
        UNION ALL
        SELECT  4, 2
        )
SELECT  (
        SELECT  CAST(asset_id AS VARCHAR(10)) + '.' AS [text()]
        FROM    rows ri
        WHERE   ri.customer_id = ro.customer_id
        ORDER BY
                asset_id
        FOR XML PATH('')
        ) AS assets,
        ra.*
FROM    (
        SELECT  DISTINCT customer_id
        FROM    rows
        ) ro
JOIN    rows ra
ON      ra.customer_id = ro.customer_id

Adriaan Stander 16 年前

你可以试试这个

DECLARE @Table TABLE(
    customer_id INT,
    asset_id INT
)

INSERT INTO @Table (customer_id,asset_id) SELECT 1,1
INSERT INTO @Table (customer_id,asset_id) SELECT 1,2
INSERT INTO @Table (customer_id,asset_id) SELECT 2,1
INSERT INTO @Table (customer_id,asset_id) SELECT 2,2
INSERT INTO @Table (customer_id,asset_id) SELECT 3,1
INSERT INTO @Table (customer_id,asset_id) SELECT 3,2
INSERT INTO @Table (customer_id,asset_id) SELECT 3,3
INSERT INTO @Table (customer_id,asset_id) SELECT 4,1
INSERT INTO @Table (customer_id,asset_id) SELECT 4,2

SELECT  * 
FROM    @Table

;WITH CTE AS (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_id, asset_id) RowNumber
        FROM    @Table
),
CTELinked AS (
        SELECT  customer_id,asset_id, RowNumber,
                CAST(CAST(asset_id AS VARCHAR(10)) AS VARCHAR(MAX)) Grouped
        FROM    CTE
        WHERE   RowNumber = 1
        UNION ALL
        SELECT  c.customer_id,c.asset_id, c.RowNumber,
                CAST(CTELinked.Grouped + ',' + CAST(c.asset_id AS VARCHAR(10)) AS VARCHAR(MAX))
        FROM    CTE c INNER JOIN
                CTELinked ON c.customer_id = CTELinked.customer_id AND c.RowNumber  = CTELinked.RowNumber + 1
),
CTEConcat AS (
        SELECT  CTELinked.*
        FROM    CTELinked INNER JOIN
                (
                    SELECT customer_id, MAX(RowNumber) MaxRows
                    FROM CTELinked
                    GROUP BY customer_id
                ) Maxes ON CTELinked.customer_id = Maxes.customer_id AND CTELinked.RowNumber = Maxes.MaxRows
)
SELECT  g.GroupingID,
        c.customer_id,
        t.asset_id
FROM    CTEConcat c INNER JOIN
        (
            SELECT  Grouped,
                    ROW_NUMBER() OVER(ORDER BY Grouped) GroupingID
            FROM    (
                        SELECT  DISTINCT 
                                Grouped
                        FROM    CTEConcat
                    ) sub
        ) g ON c.Grouped = g.Grouped INNER JOIN
        @Table t ON c.customer_id = t.customer_id

使用sp和光标x编辑-(

这应该有点帮助

DECLARE @customer_id INT
    DECLARE CUR CURSOR FOR 
    SELECT DISTINCT customer_id FROM @Table

    OPEN CUR
    FETCH NEXT FROM CUR INTO @customer_id

    DECLARE @CustTable TABLE(
            customer_id INT,
            assetids VARCHAR(MAX)
    )
    DECLARE @CustTableIDS TABLE(
            ID INT IDENTITY(1,1),
            assetids VARCHAR(MAX)
    )

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @ConCats AS VARCHAR(MAX)
        SET @ConCats = NULL
        SELECT  @ConCats = COALESCE(@ConCats + ',' + CAST(asset_id AS VARCHAR(50)), CAST(asset_id AS VARCHAR(50)))
        FROM    @Table
        WHERE   customer_id = @customer_id
        ORDER BY asset_id

        INSERT INTO @CustTable SELECT @customer_id, @ConCats
        IF NOT EXISTS(SELECT 1 FROM @CustTableIDS WHERE assetids = @ConCats) 
        BEGIN
            INSERT INTO @CustTableIDS SELECT @ConCats
        END

        FETCH NEXT FROM CUR INTO @customer_id
    END

    CLOSE CUR
    DEALLOCATE CUR

    SELECT  * 
    FROM    @Table

    SELECT  ctid.ID,
            ct.customer_id,
            t.asset_id 
    FROM    @CustTableIDS ctid INNER JOIN
            @CustTable ct ON ctid.assetids = ct.assetids INNER JOIN
            @Table t ON ct.customer_id = t.customer_id