CTE 和子查詢的非常奇怪的結果

March 31, 2018

WITH t1 AS (SELECT a.id,
                  AVG(standard_qty) std_avg,
                  AVG(poster_qty) pos_avg,
                  AVG(glossy_qty) gloss_avg
           FROM accounts a
           JOIN orders o
           ON a.id = o.account_id
           GROUP BY 1),

    t2 AS (SELECT MAX(std_avg) max_std_avg,
                  MAX(pos_avg) max_pos_avg , 
                  MAX(gloss_avg) max_gloss_avg
           FROM t1)

SELECT std_id , max_std_avg, pos_id , max_pos_avg, glos_id , max_gloss_avg
FROM(SELECT
       (SELECT id std_id  FROM t1,t2 WHERE std_avg = max_std_avg),
       (SELECT id pos_id  FROM t1,t2 WHERE pos_avg = max_pos_avg),
       (SELECT id glos_id FROM t1,t2 WHERE gloss_avg =max_gloss_avg)
   )foo ,t1,t2

結果：

std_id  | max_std_avg| pos_id | max_pos_avg | glos_id |  max_gloss_avg     
-----------------------------------------------------------------------
  1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
  1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
'
'
'
'
'
  1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
(350 ROWS)

但是，輸出應該只有 1 個單行：

std_id  | max_std_avg| pos_id | max_pos_avg | glos_id |  max_gloss_avg     
-----------------------------------------------------------------------
  1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258

如果有人能解釋為什麼，我將不勝感激？謝謝。

在最後一個外部查詢中，您foo與t1and交叉連接t2，而您只需要（並使用）來自fooand的列t2。
所以刪除t1連接：
WITH 
   ...
SELECT
FROM (
    ...
    ) foo, t2 ; 
整個外部查詢可以進一步簡化，因為在三個子查詢加上主查詢中，您實際上並不需要t2使用四次。您可以使用一次，仍然可以加入t1三次：
WITH t1 AS ...,
    t2 AS ...
SELECT std.id   AS std_id,   t2.max_std_avg, 
      pos.id   AS pos_id,   t2.max_pos_avg, 
      gloss.id AS gloss_id, t2.max_gloss_avg
FROM t2
    JOIN t1 AS std   ON std  .std_avg   = t2.max_std_avg
    JOIN t1 AS pos   ON pos  .pos_avg   = t2.max_pos_avg
    JOIN t1 AS gloss ON gloss.gloss_avg = t2.max_gloss_avg
;
另一種獲得相同結果但格式略有不同（3 行而不是 1 行）的方法是根本不使用連接，而是使用更多視窗函式。您還可以：
將兩個 CTE 合二為一。
accounts從第一個 CTE中刪除。如果有FOREIGN KEYfrom ordersto accounts，結果將是相同的。
新查詢：
WITH t1 AS (SELECT account_id,
                  AVG(standard_qty) AS std_avg,
                  AVG(poster_qty)   AS pos_avg,
                  AVG(glossy_qty)   AS gloss_avg,

                  MAX(AVG(standard_qty)) OVER () AS max_std_avg,
                  MAX(AVG(poster_qty))   OVER () AS max_pos_avg , 
                  MAX(AVG(glossy_qty))   OVER () AS max_gloss_avg
           FROM orders o
           GROUP BY account_id)

SELECT t1.*, 
      CASE WHEN std_avg   = max_std_avg
          THEN 'max_std_avg'   ELSE NULL
      END AS std_result,
      CASE WHEN pos_avg   = max_pos_avg
          THEN 'max_pos_avg'   ELSE NULL
      END AS pos_result,
      CASE WHEN gloss_avg = max_gloss_avg
          THEN 'max_gloss_avg' ELSE NULL
      END AS gloss_result
FROM t1
WHERE std_avg   = max_std_avg
  OR pos_avg   = max_pos_avg
  OR gloss_avg = max_gloss_avg ;