如何合併 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中文網其他相關文章！