★ FEATURED ARTICLE 前两天我接了个报表需求统计店铺最近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 核心难点拆解这个需求看着简单拆开其实有四个点动态日期统计范围不能写死要用CURRENT_DATE、CURDATE()这类函数每天自动算。日期边界最近7天到底是哪7天跨月、跨年怎么办。补零没有订单的日期要补0必须把日期序列和订单汇总LEFT JOIN。代码可维护性一旦需求从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_summaryorder_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_summaryCOALESCE补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_SUB3.1 三者关系与跨数据库差异先把名字扯清楚。CURDATE()是MySQL里的函数返回当前日期不含时间CURRENT_DATE是SQL标准里的关键字在MySQL里也可以直接写效果和CURDATE()完全一样甚至可以写作CURRENT_DATE()带括号。坦白讲在MySQL里你写哪个都行但如果你写的SQL以后要跨数据库迁移就得知道差异数据库当前日期写法七天前写法说明MySQLCURDATE() 或 CURRENT_DATEDATE_SUB(CURDATE(), INTERVAL 6 DAY)CURDATE()是MySQL特色函数PostgreSQLCURRENT_DATECURRENT_DATE - INTERVAL 6 day标准关键字不带括号SQL ServerCAST(GETDATE() AS DATE)DATEADD(day, -6, CAST(GETDATE() AS DATE))不认CURRENT_DATE这个名字OracleCURRENT_DATE 或 SYSDATESYSDATE - 6CURRENT_DATE返回会话时区日期这就是为什么我建议团队统一规范MySQL项目里就用CURDATE()简洁且一看就知道是MySQL函数如果项目是PG或未来可能迁移用CURRENT_DATE更标准。别在一个SQL里今天CURDATE()明天CURRENT_DATE风格混乱最容易埋坑。3.2 DATE_SUBMySQL日期减法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_timeCURDATE()-7 and create_timeCURDATE() group by DATE(create_time))a left join (select DATE(create_time) order_date,sum(amount) amount from orders where order_status2 and create_timeCURDATE()-7 and create_timeCURDATE() group by date(create_time)) b on a.order_dateb.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 第一版直觉写法先看我第一版直觉写出来的SQLSELECT 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;这个版本乍看没毛病但至少有四个问题DATE(create_time) BETWEEN 7天前 AND 今天写的是往前推7天加上今天一共8天日期范围错了。BETWEEN两端都闭合今天的数据只统计到00:00:00跑出来今天永远是0。没有订单的日期根本没出现在结果里业务方要的7行结果可能只出来三四行。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-09324850.502025-01-1000.002025-01-11587210.202025-01-12416300.002025-01-1300.002025-01-14779820.752025-01-15638010.40注意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_idSELECT里加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 问题排查速查表把这次实战和我之前踩过的坑整理成一张表方便你排查问题现象可能原因解决办法日期多了或少了INTERVAL参数算错比如7天写成了INTERVAL 7 DAY导致8天含今天用6 DAY不含今天用7 DAY今天数据一直是0用了闭合区间DATETIME匹配不到用起点 AND 明天没有订单的日期缺失只GROUP BY没有日期序列LEFT JOIN先生成日期序列再LEFT JOIN结果多天重复日期序列和汇总表JOIN条件用了datetime匹配两边都转成DATE类型再JOIN数据库版本报CTE语法错误MySQL 5.7或更老改用派生表或迁移到8.0查询很慢WHERE里对日期列用了函数改成范围条件利用索引7. 最后分享几个实战体会写完这条SQL我最大的感触是SQL入门的时候大家都先学SELECT、WHERE、GROUP BY觉得会写就完事了。但真正干到第三年第五年你会发现拉开差距的反而是CTE这种组织查询的能力、日期函数这种细节判断力、以及缩进注释这种看起来不起眼的工程习惯。这个订单报表做完后我特意把最终SQL贴到团队Wiki里当模板后来同事做周报、月报都是从这条SQL改的基本没再出过边界问题。另外一个心得是业务方说最近7天这种话时一定不要默认口径先问清楚含不含今天今天这个口径在不同的系统里也可能不一样有的是自然日有的是从早上8点开始的业务日这些都要落在SQL注释里。如果你也想练这些基本功建议找一条自己曾经写得很费劲的SQL用CTE重构一遍加上分层注释再让同事review一轮。把这几件事养成习惯后续写任何复杂查询都会顺手很多。
阅读完成 · 觉得有帮助？
