SQL多表关联：按日期条件切换获取x/y表对应字段的实现问题

需求说明 #

x表与y表结构完全一致仅数据不同，要求：

• 当date_formatted > '2022-10-01'时，取x表的对应字段值
• 当date_formatted < '2022-10-01'时，取y表的对应字段值

原始查询（未加入x表） #

create or replace table REGION(
    "geo_region",
    "paid_organic",
    "Desk",
    "editor",
    "has_yt",
    "news",
    "device_devicecategory"
) as 
(SELECT 
iso.region as "geo_region",
hand_tagged.PAID_ORGANIC as "paid_organic",
gsheet_editor.DESK as "Desk",
il_cms.HAS_YT as "has_yt",
y.news as "news",
y.DEVICE_DEVICECATEGORY as "device_devicecategory"

FROM y
LEFT JOIN iso
on y.geo_country = iso.COUNTRYNAME
LEFT JOIN il_cms
on y.cms_web_id = il_cms.web_id
AND y.published = il_cms.url_fragment
LEFT JOIN gsheet_editor
on il_cms.editor = gsheet_editor.editor
LEFT JOIN hand_tagged
on y.traffics = hand_tagged.traffics
WHERE y.DATE_FORMATTED > DATEADD(year,-2,current_date()));

错误查询问题分析 #

之前的修改尝试错误地在CASE WHEN中返回多组字段，SQL里CASE表达式只能返回单个值，不能一次性输出多个字段，这是语法不允许的。

正确SQL实现 #

create or replace table REGION(
    "geo_region",
    "paid_organic",
    "Desk",
    "editor",
    "has_yt",
    "news",
    "device_devicecategory"
) as 
(SELECT 
    iso.region as "geo_region",
    hand_tagged.PAID_ORGANIC as "paid_organic",
    gsheet_editor.DESK as "Desk",
    il_cms.HAS_YT as "has_yt",
    -- 对每个需要切换来源的字段单独做CASE判断
    CASE 
        WHEN COALESCE(x.date_formatted, y.date_formatted) > '2022-10-01' THEN x.news
        WHEN COALESCE(x.date_formatted, y.date_formatted) < '2022-10-01' THEN y.news
        ELSE NULL -- 处理日期等于'2022-10-01'的场景，可按需调整
    END as "news",
    CASE 
        WHEN COALESCE(x.date_formatted, y.date_formatted) > '2022-10-01' THEN x.DEVICE_DEVICECATEGORY
        WHEN COALESCE(x.date_formatted, y.date_formatted) < '2022-10-01' THEN y.DEVICE_DEVICECATEGORY
        ELSE NULL
    END as "device_devicecategory"

FROM y
-- 用LEFT JOIN避免x表无匹配时丢失y表数据
LEFT JOIN X
    on x.device = y.device
    and x.search = y.search
LEFT JOIN iso
    on y.geo_country = iso.COUNTRYNAME
LEFT JOIN il_cms
    on y.cms_web_id = il_cms.web_id
    AND y.published = il_cms.url_fragment
LEFT JOIN gsheet_editor
    on il_cms.editor = gsheet_editor.editor
LEFT JOIN hand_tagged
    on y.traffics = hand_tagged.traffics
WHERE y.DATE_FORMATTED > DATEADD(year,-2,current_date()));

关键调整说明 #

1. 对每个需要从x/y切换取值的字段（news、device_devicecategory）单独使用CASE WHEN判断，符合SQL语法规则。
2. 使用COALESCE(x.date_formatted, y.date_formatted)处理x表无匹配数据时的日期取值，确保判断逻辑稳定。
3. 将JOIN X改为LEFT JOIN X，避免y表有记录但x表无对应匹配时丢失数据。

内容的提问来源于stack exchange，提问作者KristiLuna