基于DATE子句关联5张无关联表的技术实现求助

首先，咱们先明确核心场景：5张表只有DATE字段是共有的，其中4张表存了profession（职业）信息，第5张表存着amount（金额）和userId（用户ID）。咱们要做的就是把这些数据按日期范围串起来生成报表，用INNER JOIN的话可以这么搞：

1. 精确日期匹配的基础方案 #

假设4张职业表分别叫prof_table_1、prof_table_2、prof_table_3、prof_table_4，每张表都有date和profession字段；第5张金额表叫amount_table，有date、amount、user_id字段。如果咱们需要同一日期的职业信息和金额数据关联，并且限定在某个日期范围内，可以这么写SQL：

SELECT
    a.user_id,
    a.amount,
    a.date AS record_date,
    p1.profession AS profession_from_table1,
    p2.profession AS profession_from_table2,
    p3.profession AS profession_from_table3,
    p4.profession AS profession_from_table4
FROM amount_table a
-- 依次关联4张职业表，匹配同一日期
INNER JOIN prof_table_1 p1 ON a.date = p1.date
INNER JOIN prof_table_2 p2 ON a.date = p2.date
INNER JOIN prof_table_3 p3 ON a.date = p3.date
INNER JOIN prof_table_4 p4 ON a.date = p4.date
-- 限定日期范围
WHERE a.date BETWEEN '2024-01-01' AND '2024-06-30';

注意点： #

• 如果某张职业表在某个日期没有记录，INNER JOIN会直接过滤掉该日期的所有数据（因为关联不匹配）。要是想保留有数据的部分，把INNER JOIN换成LEFT JOIN就行，还可以用COALESCE(p1.profession, '无数据')来处理空值。
• 如果表的date字段是DATETIME类型（带时分秒），要改成DATE(a.date) = DATE(p1.date)来忽略时间部分，确保日期匹配。

2. 日期范围关联的方案 #

如果业务需求不是精确日期匹配，而是职业表的日期在金额表日期的某个范围内（比如职业信息是金额发生前7天的），那调整ON子句的关联条件即可：

SELECT
    a.user_id,
    a.amount,
    a.date AS amount_date,
    p1.profession AS recent_profession,
    p1.date AS profession_date
FROM amount_table a
INNER JOIN prof_table_1 p1 
    -- 职业表日期在金额日期前7天到当天的范围内
    ON p1.date BETWEEN DATE_SUB(a.date, INTERVAL 7 DAY) AND a.date
WHERE a.date BETWEEN '2024-01-01' AND '2024-06-30';

3. 合并多表职业信息的方案 #

如果4张职业表是不同维度的（比如分别对应不同业务线的职业数据），咱们可以先把它们的职业信息按日期聚合，再和金额表关联，避免结果集过于零散：

-- 先把4张职业表的数据合并，再按日期聚合
WITH combined_professions AS (
    SELECT date, profession FROM prof_table_1
    UNION ALL
    SELECT date, profession FROM prof_table_2
    UNION ALL
    SELECT date, profession FROM prof_table_3
    UNION ALL
    SELECT date, profession FROM prof_table_4
),
daily_prof_summary AS (
    SELECT 
        date,
        -- 把同一日期的所有职业用逗号分隔合并
        GROUP_CONCAT(DISTINCT profession SEPARATOR ', ') AS all_professions
    FROM combined_professions
    GROUP BY date
)
-- 关联金额表和聚合后的职业信息
SELECT
    a.user_id,
    a.amount,
    a.date AS record_date,
    dps.all_professions
FROM amount_table a
INNER JOIN daily_prof_summary dps ON a.date = dps.date
WHERE a.date BETWEEN '2024-01-01' AND '2024-06-30';

关键提醒 #

• 给所有表的date字段加索引！这样关联查询的性能会提升很多，避免全表扫描拖慢速度。
• 先确认业务逻辑：是要精确日期匹配，还是范围匹配？是要每个表的独立职业字段，还是合并后的汇总信息？这会直接影响SQL的写法。

内容的提问来源于stack exchange，提问作者Joaquín