企业官网建设流程全解析

前两天我接了个报表需求：统计店铺最近7天的订单趋势。本来以为就是按天分组，真动手才发现，这需求把CTE、CURRENT_DATE、CURDATE()、DATE_SUB这些知识点全串起来了，顺手还治好了我对SQL缩进和注释的强迫症。当时第一版SQL我用了一连串嵌套子查询，日期边界也算错了，跑出来的结果里少了一天，还有两行日期和业务实际对不上，被运营同事当场教育了一顿。

回头把这段排查过程整理成文，我觉得比单纯背语法有用得多。如果你是刚入门SQL、对日期处理和查询结构有点发怵的读者，这篇文章能给你一套直接能抄的写法；如果你已经写了半年一年SQL，我也建议你花十分钟看看缩进和注释那一节，团队协作时这个比语法重要得多。咱们直接从那个订单需求开始，一点点把整个SQL拆开讲。

1. 案例背景：一个"很简单"的报表需求

1.1 需求描述与表结构

实际需求是这样的：运营同事每天上午会看一张日报，里面统计最近7天每天的新增订单量和销售额，要求这7天每一天都要有数据，没有订单的日期也要显示为0。注意这句"每一天都要有数据"，这几乎是所有时间序列报表都会踩的坑——直接按天GROUP BY，没有订单的日期直接消失，报表就缺行。

我先说下表结构。公司库是MySQL 8.0，订单表orders，核心字段如下：

CREATE TABLE orders ( order_id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10, 2) NOT NULL, order_status TINYINT NOT NULL COMMENT '1:待支付 2:已支付 3:已取消', create_time DATETIME NOT NULL, INDEX idx_create_time (create_time) );

这个表结构很常规，但有几个细节后面都会影响到：create_time是DATETIME类型，意味着同一天的数据会有00:00:00到23:59:59的各种时间戳；order_status字段带着业务含义，后面要筛选"已支付"订单时不能靠猜；而且create_time上有索引，查询写法不注意就会把索引浪费掉。

1.2 核心难点拆解

这个需求看着简单，拆开其实有四个点：

1. 动态日期：统计范围不能写死，要用CURRENT_DATE、CURDATE()这类函数每天自动算。
2. 日期边界：最近7天到底是哪7天，跨月、跨年怎么办。
3. 补零：没有订单的日期要补0，必须把"日期序列"和"订单汇总"LEFT JOIN。
4. 代码可维护性：一旦需求从7天变成30天，或者加个城市维度，现有SQL能不能快速改。

这四个点恰好对应标题里那几个关键词：CTE用来搭日期序列和汇总逻辑，CURRENT_DATE、CURDATE()、DATE_SUB用来算动态日期范围，缩进和注释决定这条SQL三个月后你还能不能读懂。下面逐个展开讲。

2. CTE：把一团乱麻的查询拆成积木

2.1 CTE到底是什么

CTE全称Common Table Expression，公共表表达式，MySQL 8.0、PostgreSQL、SQL Server 2005+、Oracle都支持。语法很简单：

WITH cte_name AS ( SELECT ... ) SELECT ... FROM cte_name;

它做的事情就是把一段查询先命名存起来，后面可以像表一样引用。和临时表的区别在于，CTE是语句级的，查询执行完就没了，不需要创建、删除临时表那套动作。最直观的理解方式：CTE就是给一段子查询起个名字，让你不用再写一堆难看的嵌套括号。

为什么说CTE能救命？举个例子，我之前见过的老代码里有个查询逻辑是A套B再套C，光括号就十来个，同事交接时说"这SQL我也不敢动"，每次改需求都靠猜。用CTE可以把A、B、C分别拆成三块，每一块都命名，后面想怎么拼怎么拼，读代码的人和数据库优化器都能省不少心。

2.2 案例中用CTE解决两个核心问题

这个订单需求里，CTE帮我解决了两个问题。

第一个问题是生成日期序列。我没有现成的日期维度表，也不想写一堆UNION ALL的常量，所以用递归CTE生成从6天前到今天的每一天：

WITH RECURSIVE date_series AS ( SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS stat_date UNION ALL SELECT DATE_ADD(stat_date, INTERVAL 1 DAY) FROM date_series WHERE stat_date < CURDATE() ) SELECT * FROM date_series;

这个CTE叫date_series，作用就是从6天前开始，每天加一天，一直加到昨天，所以结果是7行。关键在于UNION ALL的递归部分里有个WHERE条件stat_date < CURDATE()，没有这个条件递归会无限执行，MySQL会报递归超过1000次的错误。

第二个问题是订单汇总。我把订单按天分组统计这件事也拆成一个CTE，叫order_summary：

order_summary AS ( SELECT DATE(create_time) AS order_date, COUNT(order_id) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY) GROUP BY DATE(create_time) )

有了这两个CTE，最终查询就变得非常直白：从date_series里取每一天，LEFT JOIN order_summary，COALESCE补0。整个过程像拼拼图一样，每块都能单独看，单独验证，不用对着一个20行的嵌套子查询发呆。

2.3 CTE的使用边界

CTE虽好但也不是万能。我的经验是：

• 不超过3-5个CTE，太多说明查询本身该拆成多步了，或者该考虑建中间表。
• 递归CTE的终止条件一定要想清楚，MySQL默认递归上限是1000，写错就是慢查询。
• 同一个CTE被多次引用时，MySQL可能物化也可能merge，性能要靠EXPLAIN看执行计划才放心。
• 如果目标环境是MySQL 5.7或更老，CTE直接不能用，语法就报错。这种环境我通常用派生表先顶着，迁到8.0再重构。

3. 日期三兄弟：CURRENT_DATE、CURDATE()与DATE_SUB

3.1 三者关系与跨数据库差异

先把名字扯清楚。CURDATE()是MySQL里的函数，返回当前日期，不含时间；CURRENT_DATE是SQL标准里的关键字，在MySQL里也可以直接写，效果和CURDATE()完全一样，甚至可以写作CURRENT_DATE()带括号。坦白讲，在MySQL里你写哪个都行，但如果你写的SQL以后要跨数据库迁移，就得知道差异：

这就是为什么我建议团队统一规范：MySQL项目里就用CURDATE()，简洁且一看就知道是MySQL函数；如果项目是PG或未来可能迁移，用CURRENT_DATE更标准。别在一个SQL里今天CURDATE()明天CURRENT_DATE，风格混乱最容易埋坑。

3.2 DATE_SUB：MySQL日期减法

DATE_SUB的语法是DATE_SUB(date, INTERVAL expr unit)。date可以是一个日期、一个DATETIME，也可以是调用其他日期函数的结果。expr是要减去的数值，unit是单位，DAY、HOUR、MINUTE、MONTH、YEAR都行。所以减法要写成：

DATE_SUB(CURDATE(), INTERVAL 6 DAY)

这里最容易写错的是把INTERVAL 6 DAY写成INTERVAL 6，或者把6和DAY的顺序颠倒。语法顺序是INTERVAL 数值 单位，这个和日常英语语序是吻合的，但有人会记成DATE_SUB(CURDATE(), 6 DAY)，直接报语法错误。另外INTERVAL不仅用于DATE_SUB和DATE_ADD，在CASE表达式、GROUP BY时间桶等地方也用得上，值得花十分钟记牢。

对应的加法函数是DATE_ADD，我们最终SQL里加一天就用DATE_ADD(CURDATE(), INTERVAL 1 DAY)。有人会问为什么不用DATE_SUB(CURDATE(), INTERVAL -1 DAY)，也能算出明天，但可读性差了一截，负数和减法混在一起，别人看代码还得心算，没必要。

3.3 日期边界到底怎么算

这就是这个需求最容易翻车的地方。业务说"最近7天"，实现上通常有两种口径：

• 含今天往前推7天，也就是从6天前到今天，共7天；
• 不含今天，从7天前到昨天，共7天。

我们和运营确认后用的是第一种。起始日期是DATE_SUB(CURDATE(), INTERVAL 6 DAY)。如果你写INTERVAL 7 DAY，看起来是往前推7天，实际统计出来的日期范围是7天前到今天，一共8天。这个差距在日报里非常显眼，业务方一眼就能看出来。

另一个坑是结束边界。订单表里create_time是DATETIME，今天的数据create_time会一直滚到23:59:59。如果你只写create_time <= CURDATE()，那今天所有时刻大于00:00:00的订单都会被漏掉。正确写法是用半开区间，大于等于起始日期且小于明天：

create_time >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY)

"大于等于起点、小于终点"这条路是处理时间范围最稳的写法，比写<= 23:59:59这种形式安全，因为后者遇到毫秒级精度、跨天时区调整都可能出bug。

3.4 实战里的日期函数组合技巧

除了上面这个场景，日期函数还经常这样组合：

• 统计本月第一天的数据：DATE_FORMAT(CURDATE(), '%Y-%m-01')，或者DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY)。
• 统计上个月整月：用DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')算起点，再用DATE_ADD那个起点加1个月算终点，同样用半开区间。
• 把DATETIME截断成天：DATE(create_time)，但注意这会让create_time上的索引失效，数据量大时要改成范围条件。

这些技巧单独看都不复杂，组合起来就能应对绝大多数周期报表需求。我后来做周报、月报基本就是这一套换参数。

4. 缩进与注释：被严重低估的SQL生产力

4.1 一团乱麻的SQL长什么样

我接手过不少"祖传SQL"，长这样：

select a.order_date,a.cnt,b.amount from (SELECT DATE(create_time) order_date,COUNT(*) cnt FROM orders where create_time>CURDATE()-7 and create_time<CURDATE() group by DATE(create_time))a left join (select DATE(create_time) order_date,sum(amount) amount from orders where order_status=2 and create_time>CURDATE()-7 and create_time<CURDATE() group by date(create_time)) b on a.order_date=b.order_date order by a.order_date

这种SQL能跑，但没人愿意维护。缩进混乱导致你看不清WHERE和AND的层次，注释也几乎没有，改一个条件要上下反复确认。最要命的是，这类SQL往往把业务逻辑全挤在一行里，加一层过滤都怕改错。

SQL虽然是一门声明式语言，执行顺序由优化器决定，但代码是给人看的，顺序、层次、结构必须清晰。我见过有人觉得"反正数据库能执行，缩进无所谓"，等他自己三个月后回头改这条SQL，十分钟看不懂自己写的东西，就知道代价了。

4.2 一套可以落地的SQL书写规范

我说说我现在团队在用的规则，不复杂但很有效：

• 关键字统一大写，字段名、表名统一小写下划线，一眼能区分语法和标识符。
• SELECT后面每个字段独占一行，逗号放在行尾还是行首团队必须统一，我们选择放在行尾，迁移到别的SQL方言时不容易报错。
• FROM、JOIN、ON、WHERE、GROUP BY、ORDER BY这些关键字顶格，字段和条件相对缩进两到四格。
• 子查询和CTE的嵌套结构继续内缩一格，让"层级"一眼可见。
• 操作符前后加空格，AND/OR单独成行，条件多时要加括号分组。
• 数字、字符串常量不要裸写，用注释说明业务含义，比如AND order_status = 2 -- 已支付。

这套规范最大的好处是：代码评审时不用猜，扫一眼就知道每个区块在干什么。

4.3 注释是写给后来人（和自己）的

注释的粒度，我主张三层：

第一层，SQL文件或查询头部的总注释。写清楚这条SQL服务于什么报表、创建人、创建日期、最近修改记录、依赖的库表，以及需求方是谁。改一次就更新一次，别偷懒。

第二层，CTE或子查询的块注释。每个CTE前面一行注释，说明这一块是干什么的。我们案例里两个CTE名字date_series和order_summary已经比较自解释，但注释两句"生成最近7天日期序列"、"按天聚合订单量&销售额"对后来人更友好。

第三层，行内注释。只解释业务逻辑和特殊参数，不解释SQL语法。比如"-- 只统计已支付订单"，别人就知道为什么这里有WHERE条件。

MySQL里注释有三种写法：-- 开头是标准单行注释，注意MySQL要求--后面至少跟一个空格；#是MySQL特有的单行注释；/* */是多行注释。如果你在Hive、Spark SQL里写--注释，也要保持--后面带空格，否则有的版本解析会有问题。团队统一用--，配合中文，干净也兼容性最好。

4.4 从实战看注释的价值

回到这个项目，我第一版SQL写成之后被自己改过三回：第一次边界算错，第二次发现漏了LEFT JOIN，第三次想加订单状态过滤。如果没有前面那段总体注释和CTE注释，每一次改动我都得从头捋一遍。真实世界里的SQL迭代就是这么频繁——今天加一个筛选，明天维度从日期变成城市分组。代码结构清晰、注释到位，改动的成本至少能省一半。另外如果项目里用MyBatis Plus这类框架，实体类注解可以自动生成建表语句，但真正复杂的查询SQL还是得靠手工精雕细琢，缩进注释的规范在这种手写SQL里尤其重要。

5. 完整实战：从需求到最终SQL的推导

5.1 第一版：直觉写法

先看我第一版直觉写出来的SQL：

SELECT DATE(create_time) AS order_date, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE DATE(create_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() GROUP BY DATE(create_time) ORDER BY order_date;

这个版本乍看没毛病，但至少有四个问题：

1. DATE(create_time) BETWEEN 7天前 AND 今天，写的是往前推7天，加上今天一共8天，日期范围错了。
2. BETWEEN两端都闭合，今天的数据只统计到00:00:00，跑出来今天永远是0。
3. 没有订单的日期根本没出现在结果里，业务方要的7行结果可能只出来三四行。
4. WHERE里用DATE()包住create_time，索引失效，订单表几百万行时性能很难看。

5.2 第二版：引入日期序列和CTE

那我们把需求重新拆解一遍。第一步，定义日期范围。今天用CURDATE()，起始日用DATE_SUB(CURDATE(), INTERVAL 6 DAY)，结束边界用明天DATE_ADD(CURDATE(), INTERVAL 1 DAY)。

第二步，生成日期序列。用递归CTE，一天一行。起始日期作为锚点，递归部分用DATE_ADD加一天，终止条件就是加到今天为止。

第三步，汇总订单。用第二个CTE order_summary，对create_time做范围过滤，按DATE(create_time)分组，统计COUNT和SUM。注意COUNT用order_id而不是COUNT(*)，语义更清晰，也不会因为NULL产生歧义。

第四步，最终查询。date_series LEFT JOIN order_summary，没有订单的日期用COALESCE补0。

5.3 最终SQL

-- 报表：最近7天每日订单量与销售额 -- 口径：含今天，从6天前到今天的闭区间；无订单日期补0 -- 创建日期：2025-01-15 WITH RECURSIVE date_series AS ( -- 生成最近7天日期序列 SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS stat_date UNION ALL SELECT DATE_ADD(stat_date, INTERVAL 1 DAY) FROM date_series WHERE stat_date < CURDATE() ), order_summary AS ( -- 按天聚合订单量与销售额，只统计已支付订单 SELECT DATE(create_time) AS order_date, COUNT(order_id) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND order_status = 2 -- 已支付 GROUP BY DATE(create_time) ) SELECT d.stat_date AS 日期, COALESCE(s.order_cnt, 0) AS 订单量, COALESCE(s.total_amount, 0.00) AS 销售额 FROM date_series d LEFT JOIN order_summary s ON d.stat_date = s.order_date ORDER BY d.stat_date;

执行结果示意：

注意2025-01-10和2025-01-13这两天，业务侧反馈确实没有任何已支付订单，但报表里依然有行，这就是LEFT JOIN和COALESCE的价值。

5.4 把参数化讲清楚

这条SQL最大的优点是改起来快。如果下周运营要看最近30天的报表，只需要把date_series的起始日期从INTERVAL 6 DAY改成INTERVAL 29 DAY，结束条件保持不变，其他逻辑一行不用动。如果还要按城市分组，就在order_summary的GROUP BY里加city_id，SELECT里加city_name，再在外层把维度字段带出来。这就是结构清晰带来的可维护性。

有人可能会问，为什么日期序列不用数字表或者information_schema拼出来？也可以，但递归CTE在MySQL 8.0里语义最直观，7天这种小量级执行毫秒级完成，没必要绕路。等哪天要生成连续365天的日期序列，再考虑用数字表或者专门的日期维度表也不迟。

6. 常见问题与排查技巧实录

6.1 语法错误先看版本和关键字

CTE报语法错误90%是版本不支持或关键字写错。MySQL 5.7不支持WITH，直接报1064语法错误；MySQL 8.0的递归CTE必须写WITH RECURSIVE，漏掉RECURSIVE也会报错。如果公司库版本不统一，写完CTE先确认目标库是8.0。

排查顺序也有讲究：先单独执行最内层SELECT，确认没问题再一层层往外包。比如先跑SELECT * FROM date_series，确认日期序列正确，再跑order_summary，最后跑外层JOIN。数据库报错信息有时候很隐晦，这种逐层排查最省时间。

6.2 日期结果与预期对不上

先确认CURDATE()返回的日期对不对，特别是数据库服务器时区。用这条SQL看一眼：

SELECT CURDATE(), NOW(), @@session.time_zone;

如果业务方要的"今天"和数据库服务器时区不一致，CURDATE()返回的结果就会差一天。跨时区项目里建议在应用层统一把日期传进SQL，不要依赖数据库本地时区。另外还要确认一下CURDATE()返回的是date类型，在MySQL里它是date，直接和DATE_SUB运算没问题；但如果你拿到的是datetime，记得CAST成date再做日期维度匹配。

6.3 补零的雷

LEFT JOIN之后补0一定要用COALESCE。但如果汇总CTE里SELECT的是SUM(amount)，SUM本身对没数据的分组返回NULL，而COUNT对没数据的分组返回0，这两者不能混着补。COALESCE的返回类型也要统一，比如销售额补0要写成COALESCE(s.total_amount, 0.00)，保持小数位数一致，别写成一个没有小数的整数，报表侧格式化时可能差一位。

6.4 性能问题和慢SQL优化

日期范围过滤避免对create_time用函数包裹，改用create_time >= 某日和create_time < 某日这种范围条件，让索引走得更顺。数据量大时用EXPLAIN看是否全表扫描、是否Using index condition。日期序列如果固定N天，也可以用数字表JOIN，比递归CTE在极端情况下的消耗更可控；但7天这种小量级，递归CTE完全够用。

如果你在慢SQL日志里看到这条查询，多半是有人在WHERE里写了DATE(create_time) = CURDATE()，导致create_time索引失效。优化手段就是把条件改成create_time >= CURDATE() AND create_time < CURDATE() + INTERVAL 1 DAY，几乎立竿见影。

6.5 问题排查速查表

把这次实战和我之前踩过的坑整理成一张表，方便你排查：

7. 最后分享几个实战体会

写完这条SQL我最大的感触是：SQL入门的时候大家都先学SELECT、WHERE、GROUP BY，觉得会写就完事了。但真正干到第三年第五年，你会发现拉开差距的反而是CTE这种组织查询的能力、日期函数这种细节判断力、以及缩进注释这种看起来不起眼的工程习惯。

这个订单报表做完后，我特意把最终SQL贴到团队Wiki里当模板，后来同事做周报、月报都是从这条SQL改的，基本没再出过边界问题。另外一个心得是，业务方说"最近7天"这种话时，一定不要默认口径，先问清楚含不含今天；"今天"这个口径在不同的系统里也可能不一样，有的是自然日，有的是从早上8点开始的业务日，这些都要落在SQL注释里。

如果你也想练这些基本功，建议找一条自己曾经写得很费劲的SQL，用CTE重构一遍，加上分层注释，再让同事review一轮。把这几件事养成习惯，后续写任何复杂查询都会顺手很多。