sql - 如何连接表，连接一些数据

Question

我有两个表:
一、指标及指标数量
2.指定boxcode的索引及索引数量。 Boxcode是一些box，其中box包含索引。

1. input table 1

item_id quantity
1       10
2       15
3       5
1       5
1       5
2       5
3       5

sum:
1 - 20
2 - 20
3 - 10



2. input table 2

item_id quantity    boxcode
1       3           abc
2       2           abc
1       8           def
3       10          ghi
1       9           ghi
2       9           def
2       8           ghi             !!!!!!!

1 item_id once on 1 boxcode

我要得到结果:

3. result

    item_id quantity    boxcodes
    1       10          abc/3, def/7
    2       15          abc/2, def/9, ghi/4
    3       5           ghi/5
    1       5           def/1, ghi/4
    1       5           ghi/5
    2       5           ghi/4                       !!!!!!!!
    3       5           ghi/5

表 1 中的记录必须按相同的顺序。
我不知道该怎么做。
有什么建议吗？

CREATE TABLE #input1
(
    rownum int,
    item_id int,
    quantity int
)

CREATE TABLE #input2
(
    item_id int,
    quantity int,
    boxcode varchar(10)
)

INSERT INTO #input1 VALUES (1,1,10)
INSERT INTO #input1 VALUES (2,2,15)
INSERT INTO #input1 VALUES (3,3,5)
INSERT INTO #input1 VALUES (4,1,5)
INSERT INTO #input1 VALUES (5,1,5)
INSERT INTO #input1 VALUES (6,2,5)
INSERT INTO #input1 VALUES (7,3,5)

INSERT INTO #input2 VALUES (1,3, 'abc')
INSERT INTO #input2 VALUES (2,2, 'abc')
INSERT INTO #input2 VALUES (1,8, 'def')
INSERT INTO #input2 VALUES (3,10, 'ghi')
INSERT INTO #input2 VALUES (1,9, 'ghi')
INSERT INTO #input2 VALUES (2,9, 'def')
INSERT INTO #input2 VALUES (2,8, 'ghi')

select * from #input1
select * from #input2

drop table #input1
drop table #input2

result

谢谢，

Answer 1

奇怪，但它有效:

;WITH rec1 AS (
    SELECT  rownum,
            item_id,
            1 as q,
            1 as [Level],
            quantity
    from #input1
    UNION ALL
    SELECT  r.rownum,
            r.item_id,
            1,
            [Level] + 1,
            i.quantity
    FROM rec1 r
    INNER JOIN #input1 i
        ON r.rownum = i.rownum AND r.item_id = i.item_id
    WHERE [Level] < i.quantity
), rec2 AS (
    SELECT  boxcode,
            item_id,
            1 as q,
            1 as [Level],
            quantity
    from #input2
    UNION ALL
    SELECT  r.boxcode,
            r.item_id,
            1,
            [Level] + 1,
            i.quantity
    FROM rec2 r
    INNER JOIN #input2 i
        ON r.boxcode = i.boxcode AND r.item_id = i.item_id
    WHERE [Level] < i.quantity
), cte1 AS (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY item_id, rownum) as rn
    FROM rec1
), cte2 AS (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY item_id, boxcode) as rn
    FROM rec2
), final AS (
    SELECT  c1.rownum,
            c1.item_id,
            c1.quantity,
            c2.boxcode+'/'+CAST(SUM(c2.q) as nvarchar(10)) as boxcodes
    FROM cte1 c1
    INNER JOIN cte2 c2
        ON c1.item_id = c2.item_id and c1.rn = c2.rn
    GROUP BY c1.rownum, c1.item_id, c1.quantity, c2.boxcode
)

SELECT DISTINCT 
                f.rownum,
                f.item_id,
                f.quantity,
                STUFF((
                    SELECT ', '+f1.boxcodes
                    FROM final f1
                    WHERE f1.rownum = f.rownum 
                        AND f1.item_id = f.item_id 
                        AND f1.quantity = f.quantity
                    FOR XML PATH('')
                ),1,2,'') boxcodes
FROM final f

您提供的数据集的输出:

rownum  item_id quantity    boxcodes
1       1       10          abc/3, def/7
2       2       15          abc/2, def/9, ghi/4
3       3       5           ghi/5
4       1       5           def/1, ghi/4
5       1       5           ghi/5
6       2       5           ghi/4
7       3       5           ghi/5

主要思想是在两个表中分散一个小零件 1 的数量。比添加行号，然后加入并得到结果。