sql - 在 postgres 查询中替换 SELECT 返回值

Question

我在 postgres 模型查询中有一个问题要解决。

下面的查询返回一个包含三列的表:“central”、“imsi”和“mapver”。有时，查询会在“mapver”中返回空白值，但我不能让其中存在空白。|我怎样才能让这个查询用“-”或一个词代替空白，比如“WrongBlank”？它是一个 varchar 字段。

SELECT Test_Configs.central, Test_Configs.imsi, 
       Test_Configs.mapver      

FROM config_imsis_centrais AS Default_Configs              -- Valores padrão da central correta
    LEFT JOIN config_imsis_centrais AS Test_Configs        -- Valores das centrais a serem testadas
        ON Default_Configs.central = 'ZBLM04'
        AND Default_Configs.ts = (SELECT MAX(ts) FROM config_imsis_centrais)
        AND Default_Configs.imsi = Test_Configs.imsi
        AND Default_Configs.ts = Test_Configs.ts
        AND Test_Configs.central <> Default_Configs.central
WHERE (                                                    -- Análise:
            COALESCE(Default_Configs.mapver, 'null') <> COALESCE(Test_Configs.mapver, 'null') AND
            Test_Configs.central <> ''
       )

一个更简单的例子...我经常得到:

central  |   imsi   |   mapver
--------------------------------
ZSPO03   |   74402  |   
ZSPO03   |   74401  |   
ZSPO03   |   72434  |   
ZSPO03   |   72415  |

但是我想要:

central  |   imsi   |   mapver
--------------------------------
ZSPO03   |   74402  |     -
ZSPO03   |   74401  |     -
ZSPO03   |   72434  |     -
ZSPO03   |   72415  |     -

非常感谢!

Answer 1

http://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL

COALESCE 函数返回第一个不为空的参数。仅当所有参数都为 null 时才返回 Null。它通常用于在检索数据以显示时用默认值替换空值，例如:

SELECT COALESCE(description, short_description, '(none)')

文本常量永远不能为空。所以，如果它前面的东西是空的，文本字符串将被返回。

SELECT Test_Configs.central, Test_Configs.imsi, 
       COALESCE(Test_Configs.mapver, 'whatever string you want')      

FROM config_imsis_centrais AS Default_Configs              -- Valores padrão da central correta
    LEFT JOIN config_imsis_centrais AS Test_Configs        -- Valores das centrais a serem testadas
        ON Default_Configs.central = 'ZBLM04'
        AND Default_Configs.ts = (SELECT MAX(ts) FROM config_imsis_centrais)
        AND Default_Configs.imsi = Test_Configs.imsi
        AND Default_Configs.ts = Test_Configs.ts
        AND Test_Configs.central <> Default_Configs.central
WHERE (                                                    -- Análise:
            COALESCE(Default_Configs.mapver, 'null') <> COALESCE(Test_Configs.mapver, 'null') AND
            Test_Configs.central <> ''
       )

编辑，有空字符串，不是空值

引用:http://www.postgresql.org/docs/9.3/static/functions-conditional.html#FUNCTIONS-CASE

SELECT Test_Configs.central, Test_Configs.imsi, 
       CASE Test_Configs.mapver WHEN '' THEN '-'
           ELSE COALESCE(Test_Configs.mapver, '-') 
       END AS mapver  

FROM config_imsis_centrais AS Default_Configs              -- Valores padrão da central correta
    LEFT JOIN config_imsis_centrais AS Test_Configs        -- Valores das centrais a serem testadas
        ON Default_Configs.central = 'ZBLM04'
        AND Default_Configs.ts = (SELECT MAX(ts) FROM config_imsis_centrais)
        AND Default_Configs.imsi = Test_Configs.imsi
        AND Default_Configs.ts = Test_Configs.ts
        AND Test_Configs.central <> Default_Configs.central
WHERE (                                                    -- Análise:
            COALESCE(Default_Configs.mapver, 'null') <> COALESCE(Test_Configs.mapver, 'null') AND
            Test_Configs.central <> ''
       )