如何将两个查询合并为一个查询

Question

我试图将这两个查询合并为一个。有人能帮忙吗？

Query 1:
--------
SELECT 
  COUNT(Receiving_Log.Receiving_Log_Key) AS [Unresolved  Issues], 
  HUB.HUB_Name, 
  AVG(DATEDIFF(day, Receiving_Log.Rec_Date, GETDATE())) AS Avg
FROM HUB INNER JOIN Receiving_Log ON HUB.HUB_Key = Receiving_Log.HUB_Key
WHERE (Receiving_Log.Resolved = 0 )
GROUP BY HUB.HUB_Name

Query 2:
--------    
SELECT 
  COUNT(Receiving_Log.urgent) AS [Urgent  Issues], 
  HUB.HUB_Name, 
  AVG(DATEDIFF(day, Receiving_Log.Rec_Date, GETDATE())) AS Avg
FROM HUB INNER JOIN Receiving_Log ON HUB.HUB_Key = Receiving_Log.HUB_Key
WHERE (Receiving_Log.urgent = 0 and Receiving_Log.Resolved = 0)
GROUP BY HUB.HUB_Name

Answer 1

由于惟一使查询不同的是where子句中使用的条件，所以您应该能够将条件移到case表达式中，并这样做：

SELECT        
    SUM(CASE WHEN Receiving_Log.urgent = 0 AND Receiving_Log.Resolved = 0 THEN 1 ELSE 0 END) AS [Urgent  Issues], 
    SUM(CASE WHEN Receiving_Log.Resolved = 0 THEN 1 ELSE 0 END) AS [Unresolved  Issues], 
    HUB.HUB_Name, 
    AVG(DATEDIFF(day, CASE WHEN Receiving_Log.Resolved = 0 THEN Receiving_Log.Rec_Date END, GETDATE())) AS Avg_UNRESOLVED, 
    AVG(DATEDIFF(day, CASE WHEN Receiving_Log.urgent = 0 AND Receiving_Log.Resolved = 0 THEN Receiving_Log.Rec_Date END, GETDATE())) AS Avg_URGENT
FROM HUB 
INNER JOIN Receiving_Log ON HUB.HUB_Key = Receiving_Log.HUB_Key
GROUP BY HUB.HUB_Name;

这将产生如下结果：

Urgent Issues | Unresolved Issues | HUB_Name | Avg_UNRESOLVED | Avg_URGENT

另一个选项是使用union all合并两个查询的结果。