MariaDB中如何基于父lookup table的ID值条件关联多个lookup table

问题背景 #

需要编写SQL语句从多个Lookup表中获取数据，核心需求是根据主Lookup表的月份ID，动态关联对应的月份周表。此前周数据合并在一个表中，现已拆分为12个独立的月份周表（如Jan_weeks、Feb_weeks等），需调整查询逻辑适配该变化。

原有SQL（拆分前） #

拆分前周数据集中存储，查询逻辑如下：

SELECT main_table.id AS `id`,
       month_table.Months AS `Month`,
       week_table.week AS `Week`,
       main_table.timestamp
FROM main_table
INNER JOIN month_table ON main_table.Month = month_table.id
INNER JOIN week_table ON main_table.Week = week_table.id

表结构 #

主Lookup表（month_table） #

需优先关联，用于判断月份：

月份周表示例（Jan_weeks） #

月份周表示例（Feb_weeks） #

主表（main_table） #

期望结果 #

可行解决方案 #

方案1：LEFT JOIN + CASE判断（推荐） #

通过LEFT JOIN关联所有月份周表，再用CASE语句根据月份ID选择对应周文本，适配MySQL/MariaDB环境：

SELECT 
    main_table.id AS `id`,
    month_table.Months AS `Month`,
    CASE month_table.id
        WHEN 1 THEN Jan_weeks.jan_weeks
        WHEN 2 THEN Feb_weeks.feb_weeks
        WHEN 3 THEN Mar_weeks.mar_weeks
        WHEN 4 THEN Apr_weeks.apr_weeks
        WHEN 5 THEN May_weeks.may_weeks
        WHEN 6 THEN Jun_weeks.jun_weeks
        WHEN 7 THEN Jul_weeks.jul_weeks
        WHEN 8 THEN Aug_weeks.aug_weeks
        WHEN 9 THEN Sep_weeks.sep_weeks
        WHEN 10 THEN Oct_weeks.oct_weeks
        WHEN 11 THEN Nov_weeks.nov_weeks
        WHEN 12 THEN Dec_weeks.dec_weeks
    END AS `Week`,
    main_table.timestamp
FROM main_table
INNER JOIN month_table ON main_table.Month = month_table.id
LEFT JOIN Jan_weeks ON main_table.Week = Jan_weeks.id AND month_table.id = 1
LEFT JOIN Feb_weeks ON main_table.Week = Feb_weeks.id AND month_table.id = 2
LEFT JOIN Mar_weeks ON main_table.Week = Mar_weeks.id AND month_table.id = 3
LEFT JOIN Apr_weeks ON main_table.Week = Apr_weeks.id AND month_table.id = 4
LEFT JOIN May_weeks ON main_table.Week = May_weeks.id AND month_table.id = 5
LEFT JOIN Jun_weeks ON main_table.Week = Jun_weeks.id AND month_table.id = 6
LEFT JOIN Jul_weeks ON main_table.Week = Jul_weeks.id AND month_table.id = 7
LEFT JOIN Aug_weeks ON main_table.Week = Aug_weeks.id AND month_table.id = 8
LEFT JOIN Sep_weeks ON main_table.Week = Sep_weeks.id AND month_table.id = 9
LEFT JOIN Oct_weeks ON main_table.Week = Oct_weeks.id AND month_table.id = 10
LEFT JOIN Nov_weeks ON main_table.Week = Nov_weeks.id AND month_table.id = 11
LEFT JOIN Dec_weeks ON main_table.Week = Dec_weeks.id AND month_table.id = 12

注：LEFT JOIN时添加month_table.id = N条件，可过滤无效关联数据，提升查询效率。

方案2：UNION ALL拼接查询 #

若单月份数据量较大，可拆分每个月份的查询后用UNION ALL合并结果，每个子查询仅关联必要表：

SELECT 
    main_table.id AS `id`,
    month_table.Months AS `Month`,
    Jan_weeks.jan_weeks AS `Week`,
    main_table.timestamp
FROM main_table
INNER JOIN month_table ON main_table.Month = month_table.id AND month_table.id = 1
INNER JOIN Jan_weeks ON main_table.Week = Jan_weeks.id

UNION ALL

SELECT 
    main_table.id AS `id`,
    month_table.Months AS `Month`,
    Feb_weeks.feb_weeks AS `Week`,
    main_table.timestamp
FROM main_table
INNER JOIN month_table ON main_table.Month = month_table.id AND month_table.id = 2
INNER JOIN Feb_weeks ON main_table.Week = Feb_weeks.id

-- 其余10个月份的查询逻辑依次类推

方案3：重构表结构（长期最优） #

若允许调整表结构，建议将12个月份周表合并为一张统一周表，新增month_id字段关联主Lookup表：

重构后查询逻辑回归简洁，维护成本更低：

SELECT 
    main_table.id AS `id`,
    month_table.Months AS `Month`,
    week_table.week_text AS `Week`,
    main_table.timestamp
FROM main_table
INNER JOIN month_table ON main_table.Month = month_table.id
INNER JOIN week_table ON main_table.Week = week_table.id AND main_table.Month = week_table.month_id

数据库环境详情 #

• 数据库类型：MariaDB 10.5.27（兼容MySQL）
• 服务器OS：debian-linux-gnu
• 连接方式：TCP/IP

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