如何合并 SQL 表并确保日期值唯一？

合并具有唯一日期的表

在 SQL 中，合并两个表同时维护唯一日期值是一项常见任务。以下是合并 Inbound 和 Outbound 表问题的解决方案：

第一步是使用 UNION ALL 运算符合并表。此运算符组合两个表中的行而不删除重复项：

(SELECT Inbound_Date As Date, Product, SUM(Quantity) as Inbound, 0 as Outbound
FROM Inbound
GROUP BY 1,2
) 

UNION ALL

(SELECT Outbound_Date, Product, 0 as Inbound, COUNT(*)  as Outbound 
FROM Outbound
GROUP BY 1,2
)

接下来，我们需要消除重复的日期。为此，我们使用 GROUP BY 子句来合并具有相同日期和产品的行：

SELECT Date, Product, SUM(Inbound) as Inbound, SUM(Outbound) as Outbound
FROM ((SELECT Inbound_Date As Date, Product, SUM(Quantity) as Inbound, 0 as Outbound
      FROM Inbound
      GROUP BY 1,2
     ) UNION ALL
     (SELECT Outbound_Date, Product, 0 as Inbound, COUNT(*)  as Outbound 
      FROM Outbound
      GROUP BY 1,2
     )
    ) io
GROUP BY Date, Product;

根据需要，此最终查询会生成一个具有唯一日期值的表。输出将类似于您提供的示例：

Date           Product       Inbound        Outbound
2017-05-13     Product A     400            1
2017-09-04     Product C     380            0
2017-10-18     Product C      0             1
:              :             :              :
:              :             :              :
2018-09-10     Product B     200            1
:              :             :              :
:              :             :              :

以上是如何合并 SQL 表并确保日期值唯一？的详细内容。更多信息请关注PHP中文网其他相关文章！