sql - DB2 SQL 连接和计数

Question

我试图将结果组合如下：

当前结果：

BOOKING_NUM | TYPE 1 | TYPE 2
Ref1        | 3      | (null)
Ref1        | (null) | 6

预期结果：

BOOKING_NUM | TYPE 1 | TYPE 2
Ref1        | 3      | 6

我对以下查询很感兴趣：

SELECT
    BEC.BOOKING_NUM
    ,CASE WHEN BEC.TYPE = '1' THEN COUNT(BEC.TYPE) END "Type 1"
    ,CASE WHEN BEC.TYPE = '2' THEN COUNT(BEC.TYPE) END "Type 2"
FROM    DB.EXCOM BEC
WHERE
    BEC.BOOKING_NUM = 'Ref1'
GROUP BY
    BEC.TYPE,
    BEC.BOOKING_NUM
ORDER BY
    BEC.BOOKING_NUM

我很想寻求任何建议。非常感谢。

托尼

标签： sqldb2

Answer 1

您需要在max()

SELECT
     BEC.BOOKING_NUM
    ,max(CASE WHEN BEC.TYPE = '1' THEN BEC.TYPE END) "Type 1"
    ,max(CASE WHEN BEC.TYPE = '2' THEN BEC.TYPE END) "Type 2"
FROM    DB.EXCOM BEC
WHERE
    BEC.BOOKING_NUM = 'Ref1'
GROUP BY
    BEC.BOOKING_NUM
ORDER BY
    BEC.BOOKING_NUM