如何用SQL查询实现商品出入库数据的合并展示？

问题背景 #

我拥有两张数据表：Purchases（采购表）和Sales（销售表），表结构及数据如下：

Purchases表 #

-----------------------------------
date          product_id        qty
-----------------------------------
2023-07-01    10                  2
2023-07-03    10                  2
2023-07-05    10                  1

Sales表 #

-----------------------------------
date          product_id        qty
-----------------------------------
2023-07-02    10                  1
2023-07-03    10                  1
2023-07-06    10                  1

希望得到如下格式的输出：

---------------------------------------------
date          product_id        in        out
---------------------------------------------
2023-07-01    10                  2         -
2023-07-02    10                  -         1
2023-07-03    10                  2         -
2023-07-03    10                  -         1
2023-07-05    10                  1         -
2023-07-06    10                  -         1

请问是否可以通过SQL Query实现该需求？

解决方案 #

完全可以实现，核心思路是通过UNION ALL合并两张表的数据，用条件判断分别填充采购（in）和销售（out）字段，最后按日期排序。

实现SQL #

SELECT 
    date,
    product_id,
    CASE WHEN source = 'purchase' THEN qty ELSE '-' END AS `in`,
    CASE WHEN source = 'sale' THEN qty ELSE '-' END AS `out`
FROM (
    -- 提取采购数据并标记来源
    SELECT date, product_id, qty, 'purchase' AS source
    FROM Purchases
    UNION ALL
    -- 提取销售数据并标记来源
    SELECT date, product_id, qty, 'sale' AS source
    FROM Sales
) combined_data
-- 按日期排序，同一天先显示采购记录
ORDER BY date, source DESC;

逻辑说明 #

1. 数据合并：用UNION ALL将采购表和销售表的记录合并，同时新增source字段区分记录类型（采购/销售）；
2. 字段填充：通过CASE语句，为采购记录填充in列的数量，out列显示-；为销售记录填充out列的数量，in列显示-；
3. 排序规则：按date升序排列，同一日期下通过source DESC让采购记录（purchase）排在销售记录（sale）之前，和示例输出的顺序一致。

内容的提问来源于stack exchange，提问作者Jimmy