根据 FULL JOIN 结果的值合并不同表中的列-解网

问：

我有两个表，都有三列，都有一个周数和产品类别，一个有每周的传入金额，另一个有所述产品类别的传出金额。我希望将这两个表联接起来，以便获得一个包含每周每个产品类别的传入和传出金额的表。

数据如下所示：

week_number	product_category	incoming_amount
1	猫1	5
4	猫2	6
4	猫2	2
4	猫3	6
11	猫1	6
11	猫3	4

week_number	product_category	outgoing_amount
2	猫1	5
3	猫2	6
4	猫2	1
4	猫2	7
15	猫1	6
15	猫1	4

当我连接这两个表并使用以下代码对列进行分组以求和传入和传出金额时，我得到了下表作为结果。

SELECT i.week_number 
      ,i.product_category 
      ,o.week_number 
      ,o.product_category 
      ,SUM(i.incoming_amount ) AS sum_incoming_amount 
      ,SUM(o.outgoing_amount ) AS sum_outgoing_amount 
FROM incoming AS i
FULL OUTER JOIN outgoing AS o
ON i.week_number = o.week_number AND i.product_category = o.product_category 
GROUP BY i.product_category, i.week_number, o.product_category, o.week_number;

week_number	product_category	week_number	product_category	incoming_amount	outgoing_amount
1	猫1	零	零	5	零
零	零	2	猫1	零	5
零	零	3	猫2	零	6
4	猫2	4	猫2	8	8
4	猫3	零	零	6	零
11	猫1	零	零	6	零
11	猫3	零	零	4	零
零	零	15	猫1	零	10

在我试图实现的输出中，周数和产品类别列合并如下：

week_number	product_category	incoming_amount	outgoing_amount
1	猫1	5	零
2	猫1	零	5
3	猫2	零	6
4	猫2	8	8
4	猫3	6	零
11	猫1	6	零
11	猫3	4	零
15	猫1	零	10

我怎样才能做到这一点？

sql-server 合并 outer-join

WITH cte_incoming AS (
  SELECT week_number, product_category, SUM(incoming_amount) AS sum_incoming_amount
  FROM incoming
  GROUP BY week_number, product_category
),
cte_outgoing AS (
  SELECT week_number, product_category, SUM(outgoing_amount) AS sum_outgoing_amount
  FROM outgoing
  GROUP BY week_number, product_category
)
SELECT COALESCE(i.week_number, o.week_number) AS week_number,
         COALESCE(i.product_category, o.product_category) AS product_category,
         sum_incoming_amount,
         sum_outgoing_amount
FROM cte_incoming AS i
FULL OUTER JOIN cte_outgoing AS o
ON i.week_number = o.week_number AND i.product_category = o.product_category

结果：

week_number product_category    sum_incoming_amount sum_outgoing_amount
1           cat1                5                   null
2           cat1                null                5
3           cat2                null                6
4           cat2                8                   8
4           cat3                6                   null
11          cat1                6                   null
11          cat3                4                   null
15          cat1                null                10

在这里演示

上一个：TypeScript 声明合并 Angular 项目中不同文件中的接口

下一个：合并到字符串第一部分的 DataFrames

根据 FULL JOIN 结果的值合并不同表中的列

Merging columns from different tables based on its values of FULL JOIN result

评论