MySQL单日期查询改范围查询：如何修改含STR_TO_DATE的SQL语句？

没问题，我帮你把这个单日期查询改成支持日期范围的版本，还顺手优化了重复子查询的问题——原来的写法多次重复扫描stocks和loadings表，效率会比较低，优化后只需要扫描这两个表各一次，性能和可读性都会更好。

核心修改点 #

• 把原来的DATE(xxx.created_at) = 单日期条件，替换为DATE(xxx.created_at) BETWEEN 开始日期 AND 结束日期（闭区间，包含首尾日期）
• 用JOIN提前计算所有需要的聚合值，避免重复子查询
• 保留你原来的STR_TO_DATE日期格式转换逻辑，确保和原代码兼容

修改后的完整SQL #

SELECT 
    p.id,
    p.name,
    COALESCE(s_in_range.total_qty, 0) AS stock_in_range,
    COALESCE(l_in_range.total_qty, 0) AS total_loadings_range,
    COALESCE(s_total.total_qty, 0) AS total_stock_till_date,
    COALESCE(l_total.total_qty, 0) AS total_loadings_till_date,
    (COALESCE(s_before_start.total_qty, 0) - COALESCE(l_before_start.total_qty, 0)) AS opening_balance,
    (COALESCE(s_before_end.total_qty, 0) - COALESCE(l_before_end.total_qty, 0)) AS closing_balance
FROM products p
LEFT JOIN (
    SELECT pid, SUM(qty) AS total_qty
    FROM stocks
    WHERE DATE(created_at) BETWEEN STR_TO_DATE('01,4,2020','%d,%m,%Y') AND STR_TO_DATE('15,4,2020','%d,%m,%Y')
    GROUP BY pid
) s_in_range ON s_in_range.pid = p.id
LEFT JOIN (
    SELECT pid, SUM(qty) AS total_qty
    FROM loadings
    WHERE DATE(created_at) BETWEEN STR_TO_DATE('01,4,2020','%d,%m,%Y') AND STR_TO_DATE('15,4,2020','%d,%m,%Y')
    GROUP BY pid
) l_in_range ON l_in_range.pid = p.id
LEFT JOIN (
    SELECT pid, SUM(qty) AS total_qty
    FROM stocks
    GROUP BY pid
) s_total ON s_total.pid = p.id
LEFT JOIN (
    SELECT pid, SUM(qty) AS total_qty
    FROM loadings
    GROUP BY pid
) l_total ON l_total.pid = p.id
LEFT JOIN (
    SELECT pid, SUM(qty) AS total_qty
    FROM stocks
    WHERE DATE(created_at) < STR_TO_DATE('01,4,2020','%d,%m,%Y')
    GROUP BY pid
) s_before_start ON s_before_start.pid = p.id
LEFT JOIN (
    SELECT pid, SUM(qty) AS total_qty
    FROM loadings
    WHERE DATE(created_at) < STR_TO_DATE('01,4,2020','%d,%m,%Y')
    GROUP BY pid
) l_before_start ON l_before_start.pid = p.id
LEFT JOIN (
    SELECT pid, SUM(qty) AS total_qty
    FROM stocks
    WHERE DATE(created_at) <= STR_TO_DATE('15,4,2020','%d,%m,%Y')
    GROUP BY pid
) s_before_end ON s_before_end.pid = p.id
LEFT JOIN (
    SELECT pid, SUM(qty) AS total_qty
    FROM loadings
    WHERE DATE(created_at) <= STR_TO_DATE('15,4,2020','%d,%m,%Y')
    GROUP BY pid
) l_before_end ON l_before_end.pid = p.id;

细节说明 #

1. 日期范围参数：你只需要修改两个位置的日期字符串——'01,4,2020'（范围开始日期）和'15,4,2020'（范围结束日期），保持%d,%m,%Y（日,月,年）的格式即可。
2. COALESCE替代IFNULL：两者功能类似，但COALESCE是SQL标准函数，兼容性更好，如果你习惯用IFNULL也可以改回去。
3. 性能优化：通过提前分组聚合，每个表只被扫描一次，比原来的子查询写法效率高很多，数据量大的时候差异会很明显。

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