时间戳

首页 > 解决方案 > 两个 INNER JOIN 的 FULL OUTER JOIN

sql - 两个 INNER JOIN 的 FULL OUTER JOIN

问题描述

我有三张桌子。我的表Order有一个Id和一个Date。同样，我的桌子Delivery有一个Id和一个Date。我的第三张表Part有一个,和Id两个外键: , 。OrderDeliveryOrderIdDeliveryId

我想创建一个查询，让我大致了解每月的订单和交货数量，如下所示：

+------+-------+------------+---------------+
| Year | Month | OrderCount | DeliveryCount |
+------+-------+------------+---------------+
| 2021 | 2     | 10         | 12            |
+------+-------+------------+---------------+
| 2021 | 1     | 234        | 213           |
+------+-------+------------+---------------+
| ...  | ...   | ...        | ...           |
+------+-------+------------+---------------+

所以我创建了一个查询，每月给我订单：

SELECT 
  MONTH(o.[Date]) AS [Month],
  YEAR(o.[Date]) AS [YEAR],
  COUNT(p.Id) AS OrderCount
FROM           
  Part AS p
INNER JOIN
  [Order] AS o ON o.Id = p.OrderId
GROUP BY 
  MONTH(o.[Date]), YEAR(o.[Date])

一个用于每月交货：

SELECT 
  MONTH(d.[Date]) AS [Month],
  YEAR(d.[Date]) AS [YEAR],
  COUNT(p.Id) AS DeliveryCount
FROM           
  Part AS p
INNER JOIN
  Delivery AS d ON d.Id = p.DeliveryId
GROUP BY 
  MONTH(d.[Date]), YEAR(d.[Date])

但现在我正在努力加入他们。我想我需要一个FULL OUTER JOIN，因为当没有订单但交货时我需要 Month/Year/DeliveryCount，反之亦然。

我试过的是

SELECT 
  MONTH(o.Date) AS [Month]
  YEAR(o.Date) as [Year]
  COUNT(p.Id) as OrderCount
FROM           
  Part AS p
INNER JOIN
  [Order] AS o ON o.Id = p.OrderId
GROUP BY 
MONTH(o.Date), Year(o.Date)

FULL OUTER JOIN

(SELECT 
  MONTH(d.Date) AS [Month]
  YEAR(d.Date) as [Year]
  COUNT(pp.Id) as DeliveryCount
FROM           
  Part AS pp
INNER JOIN
  Delivery AS d ON d.Id = pp.DeliveryId
GROUP BY 
MONTH(d.Date), YEAR(d.Date)) AS d ON d.[Month] = MONTH(o.Date) AND d.[Year] = YEAR(o.Date)

但这不是FULL OUTER JOINS工作方式。

如何获得这两个内部联接的完整外部？或者这种外部连接两个内部连接的方法首先是错误的？

标签： sqlsql-serverjoin

解决方案

您必须将两个查询视为子查询。这应该会给你想要的结果

 Select Orders.OrderCount, Deliveries.DeliveryCount
    from (
    SELECT 
      MONTH(o.Date) AS [Month]
      YEAR(o.Date) AS [YEAR]
      COUNT(p.Id) AS OrderCount
    FROM           
      Part AS p
    INNER JOIN
      [Order] AS o ON o.Id = p.OrderId
    GROUP BY 
      MONTH(o.Date), YEAR(o.Date)
    ) Orders

 

    FULL OUTER JOIN
    
    (
    SELECT 
      MONTH(d.Date) AS [Month]
      YEAR(d.Date) AS [YEAR]
      COUNT(p.Id) AS DeliveryCount
    FROM           
      Part AS p
    INNER JOIN
      Delivery AS d ON d.Id = p.DeliveryId
    GROUP BY 
      MONTH(d.Date), YEAR(d.Date) ) Deliveries
    

    on Orders.Month = Deliveries.Month
        and Orders.Year = Deliveries.Year

推荐阅读