SQL 复杂报表生成 SQL 方法

mysql分组统计需严格遵循group by规则：非聚合字段必须出现在group by中，聚合指标用sum/avg/count等包裹；慎用表达式导致索引失效；窗口函数lag/lead替代自连接实现同比环比；union all拼接须对齐列数、顺序、类型；大中间结果优先临时表，小逻辑用cte提升可读性。

GROUP BY 配合聚合函数做基础分组统计

复杂报表往往从分组开始，但很多人直接写 select * 加 GROUP BY，结果报错“nonaggregated column”——MySQL 严格模式下，SELECT 列表里所有非聚合字段都必须出现在 GROUP BY 中。

• 只选真正需要分组维度的字段进 GROUP BY，比如按部门、月份统计，就只放 dept_id 和 DATE_FORMAT(create_time, '%Y-%m')
• 数值类指标统一用聚合函数包裹：SUM(sales)、AVG(price)、COUNT(DISTINCT user_id)
• 避免在 GROUP BY 里塞表达式（如 UPPER(name)），会导致索引失效；优先在 JOIN 前或子查询里处理

用窗口函数替代自连接实现同比/环比

查“上月销售额”“去年同期”时，别急着写两个子查询再 JOIN。自连接易出错、性能差，尤其数据量大时容易卡住。

• LAG() 和 LEAD() 直接取相邻行值，比如 LAG(SUM(amount), 1) OVER (PARTITION BY dept_id ORDER BY ym)
• 跨年对比用 LAG(SUM(amount), 12)，前提是时间字段已规整为年月粒度（如 '2024-01'）
• 注意 ORDER BY 必须明确且唯一，否则窗口结果不可靠；必要时加 id 做二级排序

UNION ALL 拼接多口径数据要对齐字段类型和顺序

把订单、退款、优惠核销三张表合并成一张“资金流水”报表时，UNION 报错“column count doesn’t match”或隐式转换出错，基本都是字段没对齐。

• 每段 SELECT 的列数、顺序、类型必须一致；字符串补 ''，数字补 0 或 NULL，别依赖数据库自动转
• 用 UNION ALL 而不是 UNION，报表场景不需要去重，省掉排序开销
• 字段别名只在第一段写，后续段不能写别名，否则语法报错

临时表 or CTE？看执行频次和中间结果大小

嵌套五层子查询的报表 SQL 可读性差，改起来像解谜。但盲目用 WITH（CTE）也不一定更好。

• CTE 在 postgresql / SQL Server 中会物化，但 MySQL 8.0 默认不物化，反复引用可能多次执行相同逻辑
• 中间结果超 10 万行，优先建临时表并加索引：CREATE TEMPORARY table tmp_sales AS ...，再基于它计算
• 如果只是为提升可读性且中间集不大，CTE 更干净；但涉及多次过滤、JOIN，临时表可控性更强

窗口函数的 ORDER BY 和聚合的 GROUP BY 看似相似，实际作用域完全不同，混用时逻辑很容易偏移。还有就是日期格式化函数（比如 DATE_FORMAT、TO_CHAR）在不同数据库里行为不一致，同一份 SQL 换个库就跑不通——这些地方不盯紧，调一整天也看不出哪错了。