主页 / dba / 问题 / 281340
Accepted
checkpoint
Asked: 2020-12-12 04:20:21 +0800 CST

如何使用 SQL CASE 结果比较一列并获得另一个案例结果

  • 772

我正在尝试将我的案例结果（desirecountry）与现有列（so.country_name）进行比较，以获得另一个案例结果（Final_Country_Map_Name）：

     SELECT subs."user email", subs."user join date", subs.location,
                iso.country_name as iso_country, iso.country_id as iso_country_id, 
                    users.country, users.event_type,
    -- 1.  
    CASE  WHEN subs.location = iso.country_name THEN iso.country_name
             ELSE users.country END AS **desirecountry**,
    -- 2.
    CASE  WHEN **desirecountry** = iso.country_name THEN **desirecountry**
            --ELSE Null END AS **Final_Country_Map_Name**
          
    FROM subs
    LEFT JOIN
    iso
    ON
    subs.location = iso.country_name
    LEFT JOIN
    users
    ON
    users.user_id = subs."user email"

当我使用第二个 CASE 条件时，逻辑失败，它没有给出预期的输出，它在“final_country_map_name”列中给了我一个空值，而不是“desirecountry”值=美国。任何小的帮助都会有所帮助，谢谢。例如：列名是：->

location | iso_country  |   country     | desirecountry  | final_country_map_name   &&& Column o/p are: -> 
U.s,      null,          United States,   United States,       null,
mysql redshift

2 个回答

  1. 你的代码看起来更像 postgre，而不是 MySQL

    另外为了您的信息，您不应该在列名中使用空格并将所需的输出留给 gui，这样可以节省大量时间

    我使用内部SubSELECT来收集数据并将第二个CASE放在外部 SELECT

    SELECT 
    `user email`,
    `user join date`,
    location,
    iso_country,
    iso_country_id,
    users.country,
    users.event_type,
    desirecountry,
    CASE
        WHEN desirecountry = iso_country THEN desirecountry
        ELSE NULL
    END AS Final_Country_Map_Name
FROM
    (SELECT 
        subs.`user email`,
            subs.`user join date`,
            subs.location,
            iso.country_name AS iso_country,
            iso.country_id AS iso_country_id,
            users.country,
            users.event_type,
            CASE
                WHEN subs.location = iso.country_name THEN iso.country_name
                ELSE users.country
            END AS desirecountry
    FROM
        subs
    LEFT JOIN iso ON subs.location = iso.country_name
    LEFT JOIN users ON users.user_id = subs.`user email`)
    • 0
  2. Best Answer

    最后我找到了答案：

    SELECT 
`user email`,
`user join date`,
location,
iso_country,
iso_country_id,
users.country,
users.event_type,
desirecountry,

    CASE 
    WHEN EXISTS (SELECT TOP 1 * FROM iso_country WHERE country_name = desirecountry) 
    THEN desirecountry
ELSE Null 
END AS Final_Country_Map_Name

    FROM (SELECT 
    subs.`user email`,
        subs.`user join date`,
        subs.location,
        iso.country_name AS iso_country,
        iso.country_id AS iso_country_id,
        users.country,
        users.event_type,
        CASE
            WHEN subs.location = iso.country_name THEN iso.country_name
            ELSE users.country
        END AS desirecountry
FROM
    subs
LEFT JOIN iso ON subs.location = iso.country_name
LEFT JOIN users ON users.user_id = subs.`user email`)
    • 0

相关问题