主页 / dba / 问题 / 290166
Accepted
Nil R
Asked: 2021-04-21 20:11:42 +0800 CST

在 UNION 中多次使用同一个表

  • 772

我得到一个查询，该查询多次使用具有相同列和过滤器的同一个表，唯一改变的是手动给出的类型。我认为必须有更好的方法来做到这一点。

查询语句如下：

    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

谢谢！

db2 db2-luw

1 个回答

  1. Best Answer

    利用

    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

    • 1

相关问题