將兩個查詢合二為一

我有兩張桌子，例如"Locations"和"Connections"

"Locations"有價值觀

Id | Dimension
---------------    
1 |    4
2 |    8
3 |    2

"Connections"維護屬性

Origin | Destination | Value | Distance_KM
-------------------------------------------
  1   |      2      |  500  |     30
  1   |      3      |  100  |     20
  2   |      1      |  100  |     10
  2   |      3      |  300  |     10
  3   |      1      |  100  |     40

我想使用以下屬性表創建輸出。"In"對應於"Destination"from"Connections"和"Out"to 的地方"Origin"。

Id | Dimension | In_Value | In_Count | In_Dist | Out_Value | Out_Count | Out_Dist
----------------------------------------------------------------------------------  
1 |     4     |    200   |     2    |    50   |    600    |    2      |     50
2 |     8     |    500   |     1    |    30   |    400    |    2      |     20 
3 |     2     |    400   |     2    |    30   |    100    |    1      |     40

我可以通過兩個查詢分別獲得我爭取的結果。

查詢 1

SELECT C.Destination, SUM(C.Value) AS In_Value, COUNT(C.Destination) AS In_Count, SUM(C.Distance_KM) AS In_Dist
FROM Connections AS C
GROUP BY C.Destination

查詢 2

SELECT C.Origin, SUM(C.Value) AS Out_Value, COUNT(C.Origin) AS Out_Count, SUM(C.Origine_KM) AS Out_Dist
FROM Connections AS C
GROUP BY C.Origin

不過，應該只有一個查詢可以解決我的問題，不是嗎？我試過這個但沒有成功。

SELECT L.Id AS Id, L.Dimension AS Dimension, C.In_Value, C.In_Count, C.In_Dist, C.Out_Value, C.Out_Count, C.Out_Dist
FROM   Locations AS L
LEFT   JOIN (
            SELECT C.Destination,
                   SUM(C.Value) AS In_Value,
                   COUNT(C.Destination) AS In_Count,
                   SUM(C.Distance_KM) AS In_Dist
            FROM Connections AS C
            GROUP BY C.Destination
       ) ON L.Id = C.Destination
LEFT JOIN (
          SELECT C.Origin,
                 SUM(C.Value) AS Out_Value,
                 COUNT(C.Origin) AS Out_Count, 
                 SUM(C.Origine_KM) AS Out_Dist
          FROM Connections AS C
          GROUP BY C.Origin
       ) ON L.Id = C.Origin

基本上，我不知道我是否有資格在LEFT JOIN ON已經存在的查詢中添加第二個LEFT JOIN ON，是嗎？

---

參考：

• 兩個 SQL LEFT JOINS 產生不正確的結果
• 在一個查詢中對多個表進行多個左連接

SELECT L.Id AS Id, L.Dimension AS Dimension, 
      sq1.In_Value, sq1.In_Count, sq1.In_Dist, 
      sq2.Out_Value, sq2.Out_Count, sq2.Out_Dist
FROM Locations AS L
LEFT JOIN (
          SELECT C.Destination, 
                 SUM(C.Value) AS In_Value,  
                 COUNT(C.Destination) AS In_Count,  
                 SUM(C.Distance_KM) AS In_Dist
          FROM Connections AS C
          GROUP BY C.Destination
        ) AS sq1 ON L.Id = sq1.Destination
LEFT JOIN (
          SELECT C.Origin,  
                 SUM(C.Value) AS Out_Value,  
                 COUNT(C.Origin) AS Out_Count,  
                 SUM(C.Distance_KM) AS Out_Dist
          FROM Connections AS C
          GROUP BY C.Origin
        ) AS sq2 ON L.Id = sq2.Origin

小提琴

引用自：https://dba.stackexchange.com/questions/231424