在 UNION 中多次使用同一個表

我得到一個查詢，該查詢多次使用具有相同列和過濾器的同一個表，唯一改變的是手動給出的類型。我認為必須有更好的方法來做到這一點。

查詢語句如下：

   Select Column 1, Column 2, Sum(Column 3) AS Test, "First Query" AS "Type"
          From Table X
          Where Column 1 = "Fly"
   Group by Column 1, Column 2
   
   UNION ALL 
   
   Select Column 1, Column 2, Sum(Column 3) AS Test, "Second Query" AS "Type"
          From Table X
          Where Column 1 = "Fly"
   Group by Column 1, Column 2

   UNION ALL 
   
   Select Column 1, Column 2, Sum(Column 3) AS Test, "Third Query" AS "Type"
          From Table X
          Where Column 1 = "Fly"
   Group by Column 1, Column 2

謝謝！

採用

Select X.Column1, X.Column2, Sum(X.Column3) AS Test, Types."Type"
From Table X
CROSS JOIN ( SELECT "First Query" AS "Type"
            UNION ALL
            SELECT "Second Query"
            UNION ALL
            SELECT "Third Query" ) AS Types
Where X.Column1 = "Fly"
Group by X.Column1, X.Column2, Types."Type"

---

可能 SELECTTypes需要額外的FROM DUAL或類似的，或者 DB2 使用另一種方​​法來選擇標量值 - 測試。

更新（從評論中複製）

您可以使用以下方法縮短 CROSS JOIN 部分：

CROSS JOIN ( values ('First Query'),('Second Query'),('Third Query')) types (type)

FWIW，Db2 還允許

CROSS JOIN ( values 'First Query','Second Query','Third Query') types (type)

但這不是我相信的標準——Lennart

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