如何在相鄰列中顯示單獨表的查詢？

我有兩張表——一張名為“計劃約束”，其中包含“sot_allowed”時間間隔，一張名為“計劃”，其中包含“sot_contribution”時間間隔。

這是兩個表的架構（為便於閱讀而編輯）：

                        Table "public.planning_constraints"
  Column    |           Type           |          Modifiers  
-------------+--------------------------+-------------------------------
start_time  | timestamp with time zone | 
end_time    | timestamp with time zone | 
sot_allowed | interval                 | 

                        Table "public.planning"
     Column      |           Type           |         Modifiers          
------------------+--------------------------+----------------------------
start_time       | timestamp with time zone | 
end_time         | timestamp with time zone | 
sot_contribution | interval                 | 

我可以單獨查詢它們並生成我想要的總數。“planning_constraints”表的查詢是：

SELECT
 date_trunc('day', start_time - INTERVAL '18 hours')::date AS planning_day,
 sum(sot_allowed) AS minutes_allowed
FROM planning_constraints
WHERE start_time>='2016-11-26 18:00:00+00' AND start_time<'2016-12-03 18:00:00+00' AND comment like '6%'
GROUP BY planning_day
ORDER BY planning_day;

產生：

planning_day | minutes_allowed 
--------------+-----------------
2016-11-26   | 01:24:00
2016-11-27   | 01:38:00
2016-11-28   | 01:29:00
2016-11-29   | 01:43:00
2016-11-30   | 01:32:00
2016-12-01   | 01:41:00
2016-12-02   | 01:43:00
(7 rows)

“計劃”表的查詢是：

SELECT
 date_trunc('day', start_time - INTERVAL '18 hours')::date AS planning_day,
 sum(sot_contribution) AS minutes_planned
FROM planning
WHERE start_time>='2016-11-26 18:00:00' AND start_time<'2016-12-03 18:00:00+00'
GROUP BY planning_day
ORDER BY planning_day;

產生：

planning_day | minutes_planned 
--------------+-----------------
2016-11-26   | 01:19:12
2016-11-27   | 01:38:23
2016-11-28   | 01:27:36
2016-11-29   | 01:40:39
2016-11-30   | 01:27:16
2016-12-01   | 01:38:28
2016-12-02   | 01:42:50
(7 rows)

我想在相鄰列中顯示這些查詢的結果，以及它們之間的區別。這是我的嘗試：

SELECT
 date_trunc('day', c.start_time - INTERVAL '18 hours')::date AS planning_day,
 sum(c.sot_allowed) AS minutes_allowed,
 sum(p.sot_contribution) AS minutes_planned,
 (sum(c.sot_allowed) - sum(p.sot_contribution)) AS diff
FROM planning_constraints c, planning p
WHERE c.start_time>='2016-11-26 18:00:00+00' AND c.start_time<'2016-12-03 18:00:00+00' AND c.comment like '6%'
AND p.start_time>='2016-11-26 18:00:00' AND p.start_time<'2016-12-03 18:00:00+00'
GROUP BY planning_day
ORDER BY planning_day;

產生：

planning_day | minutes_allowed | minutes_planned |   diff    
--------------+-----------------+-----------------+-----------
2016-11-26   | 576:48:00       | 43:37:36        | 533:10:24
2016-11-27   | 672:56:00       | 43:37:36        | 629:18:24
2016-11-28   | 611:08:00       | 43:37:36        | 567:30:24
2016-11-29   | 707:16:00       | 43:37:36        | 663:38:24
2016-11-30   | 631:44:00       | 43:37:36        | 588:06:24
2016-12-01   | 693:32:00       | 43:37:36        | 649:54:24
2016-12-02   | 707:16:00       | 43:37:36        | 663:38:24
(7 rows)

‘minutes_allowed’ 和 ‘minutes_planned’ 列不顯示正確的值。我覺得我在這裡遺漏了一些小東西，但是，我一直無法找到解決方案。

您基本上是在建構兩個表，然後必須在日期列上連接它們。在您的查詢中，您錯過了連接條件。

解決此問題的一種明確方法是將兩個結果集建構為 CTE（帶有查詢）：

WITH pc AS (SELECT planning_day, sum(sot_contribution) AS minutes_planned 
           FROM planning_constraints 
           ...),
     p AS (SELECT planning_day, sum(sot_allowed) AS minutes_allowed
           FROM planning
           ...)
SELECT planning_day, 
      COALESCE(minutes_allowed, interval '0 minute') AS allowed, 
      COALESCE(minutes_planned, interval '0 minute') AS planned, 
      COALESCE(minutes_allowed, interval '0 minute') - 
          COALESCE(minutes_planned, interval '0 minute') AS diff
 FROM pc FULL OUTER JOIN p USING (planning_day)
ORDER BY planning_day;

FULL OUTER JOIN表示如果在任一結果集中指定了某一天，則該天會有一行。如果這不是您想要的，請弄清楚您需要哪種類型的連接。

筆記：

• planning_day不需要是COALESCEd，因為USING子句會處理這個問題
• 此外，不需要GROUP BY在外部查詢中使用 a，因為planning_day這已經是兩個源表的鍵
• 現在ORDER BY可以從兩個子查詢中刪除

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