主页 / dba / 问题 / 231424
Accepted
Taras
Asked: 2019-03-06 22:47:04 +0800 CST

将两个查询合二为一

  • 772

我有两张桌子，例如"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吗？

参考：

query aggregate

2 个回答

  1. Best Answer
    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

    小提琴

    • 1

  2. 另一种可能性是连接Connections到LocationsonLocations.Id IN (Connections.Origin, Connections.Destination)并根据匹配的两列中的哪一列执行一系列条件聚合。

    这就是我的想法：

    SELECT
  l.Id
, l.Dimension
, SUM(CASE l.Id WHEN c.Destination THEN c.Value       END) AS In_Value
, SUM(CASE l.Id WHEN c.Destination THEN 1 ELSE 0      END) AS In_Count
, SUM(CASE l.Id WHEN c.Destination THEN c.Distance_KM END) AS In_Dist
, SUM(CASE l.Id WHEN c.Origin      THEN c.Value       END) AS Out_Value
, SUM(CASE l.Id WHEN c.Origin      THEN 1 ELSE 0      END) AS Out_Count
, SUM(CASE l.Id WHEN c.Origin      THEN c.Distance_KM END) AS Out_Dist
FROM
  Locations AS l
  LEFT JOIN Connections AS c ON l.Id IN (c.Origin, c.Destination)
GROUP BY
  l.Id
, l.Dimension
;

    注意：计数 ( In_Count, Out_Count) 已使用SUM函数实现，这只是为了与其余代码保持一致。您可以改用COUNTs，它看起来几乎相同，只是没有ELSE 0位：

    ...
, COUNT(CASE l.Id WHEN c.Destination THEN 1 END) AS In_Count
...
, COUNT(CASE l.Id WHEN c.Origin      THEN 1 END) AS Out_Count
...

    尽管看起来可以说更简单，但这种方法不一定更快，尤其是在较大的数据集上。但是，如果性能不是问题，此选项可能看起来更具可读性，因此更易于维护。

    该解决方案可以在这个小提琴的帮助下在线测试（借用 Akina 的小提琴的测试设置）。

    • 0

相关问题