sql - 使用一个创建两个表

Question

CREATE TABLE ItemsOfStores ( Item int, Store int );

对于我上面的示例，该数据将包含:

INSERT INTO ItemsOfStores (Item,Store)
    VALUES (1,1),(1,3),(4,1),(4,3),(5,1),(5,3),(2,1),
           (2,2),(2,4),(3,1),(3,2),(3,4);

我需要 SQL 将结果插入到 2 个表中:

CREATE TABLE StoreGroups ( GroupId int, Store int )
CREATE TABLE ItemsOfGroups ( GroupId int, Item int )

在我上面的示例中，商品 1、4 和 5 在商店 1 和 3，那么会有一组由商店 1 和 3 组成的商店，我将其称为第 1 组。因此第 1 组包含商店 1 和 3，因此会将 (1,1),(1,3) 放入 StoreGroups，项目 1、4 和 5 在该组中，因此 (1,1),(1,4),(1,5) 将进入 ItemsOfGroups。

同样在我的示例中，商品 2 和 3 在商店 1、2 和 4，那么会有第二组由商店 1、2 和 4 组成的商店，我将其称为第 2 组。因此 (2,1),(2,2),(2,4) 将进入 StoreGroups，而 (2,2),( 2,3) 将进入 ItemsOfGroups。

所以这个小例子的最终结果将填充 StoreGroups与 (1,1),(1,3),(2,1),(2,2),(2,4),和具有 (1,1),(1,4),(1,5),(2,2),(2,3) 的 ItemsOfGroups

Answer 1

我猜你想要这样的东西。

    CREATE TABLE ItemsOfStores ( Item int, Store int );


    INSERT INTO ItemsOfStores (Item,Store)
        VALUES (1,1),(1,3),(4,1),(4,3),(5,1),(5,3),(2,1),
               (2,2),(2,4),(3,1),(3,2),(3,4);


    CREATE TABLE StoreGroups ( GroupId int, Store int );
    CREATE TABLE ItemsOfGroups ( GroupId int, Item int );

    Insert into StoreGroups

    SELECT z.GroupID,  value 
    FROM (
            Select    ROW_NUMBER() OVER(ORDER BY x.Store ASC)as GroupID, x.Store , x.Item
            from 
                ( 
                    select 

                    T3.Store, 

                    stuff((SELECT  ', ' + cast(Item as varchar(10))
                            FROM (
                                    select Item, 
                                        stuff((SELECT  ', ' + cast(Store as varchar(10))
                                                FROM ItemsOfStores t2
                                                where t2.Item = t1.Item
                                                order by Store
                                                FOR XML PATH('')),1,1,'') as Store
                                    from ItemsOfStores t1
                                    group by Item
                                )t4
                            where t4.Store = t3.Store
                            order by Item
                            FOR XML PATH('')),1,1,'') 
                    as Item

                    from (
                                            select Item, 
                                              stuff((SELECT  ', ' + cast(Store as varchar(10))
                                                       FROM ItemsOfStores t2
                                                       where t2.Item = t1.Item
                                                       order by Store
                                                       FOR XML PATH('')),1,1,'') as Store
                                            from ItemsOfStores t1
                                            group by Item


                                    ) t3
                    group by T3.Store
                )  x
        )z
    CROSS APPLY STRING_SPLIT(z.Store, ',');

    Select * from StoreGroups

返回:

    GroupId Store
    1       1
    1       2
    1       4
    2       1
    2       3

同样的逻辑:

    Insert into ItemsOfGroups
    -- Use String Split (SQL2016+) function to split merged colums back into seperate values with their related GroupID
    SELECT z.GroupID,  value 
    FROM (-- Add GroupID which is related to both merged Item rows and merged store rows
            Select    ROW_NUMBER() OVER(ORDER BY x.Store ASC)as GroupID, x.Store , x.Item
            from 
                ( -- Merge Item values into one row (grouped by Grouped result of Store)
                    select Store, 
                      stuff((SELECT  ', ' + cast(Item as varchar(10))
                               FROM (
                                        select Item, 
                                          stuff((SELECT  ', ' + cast(Store as varchar(10))
                                                   FROM ItemsOfStores t2
                                                   where t2.Item = t1.Item
                                                   order by Store
                                                   FOR XML PATH('')),1,1,'') as Store
                                        from ItemsOfStores t1
                                        group by Item
                                    )t4
                               where t4.Store = t3.Store
                               order by Item
                               FOR XML PATH('')),1,1,'') as Item
                    from ( -- Merge Store values into one row (grouped by Item)
                                select Item, 
                                  stuff((SELECT  ', ' + cast(Store as varchar(10))
                                           FROM ItemsOfStores t2
                                           where t2.Item = t1.Item
                                           order by Store
                                           FOR XML PATH('')),1,1,'') as Store
                                from ItemsOfStores t1
                                group by Item


                        ) t3
                    group by T3.Store
                )  x
            )z
        CROSS APPLY STRING_SPLIT(z.Item, ',');

    Select * from ItemsOfGroups

返回:

    GroupId Item
    1       2
    1       3
    2       1
    2       4
    2       5