1. 前言
最近同事写了一段SQL(具体如下所示),其中部分写法以前未接触过,因此进行拆分学习。
SELECT
date_range.date,
COALESCE(COUNT(DISTINCT stu_sch2.create_time), 0) AS daily_orders,
COALESCE(COUNT(DISTINCT stu_sch1.send_time), 0) AS daily_bindings
FROM
(
SELECT
DATE_ADD("2023-12-11", INTERVAL seq DAY) AS date
FROM
(
SELECT
seq
FROM
(
SELECT
@row := @row + 1 as seq
FROM
information_schema.columns,
(
SELECT
@row := -1) r1
LIMIT 200) s) dates
WHERE
DATE_ADD("2023-12-11 00:00:00", INTERVAL seq DAY) <= "2024-05-10 00:00:00") date_range
LEFT JOIN
(
SELECT
s.send_time
FROM
student s
LEFT JOIN school sch ON
s.school_id = sch.id
WHERE
s.is_deleted = 0
) stu_sch1 ON
date_range.date = DATE(stu_sch1.send_time)
LEFT JOIN
(
SELECT
s.create_time
FROM
student s
LEFT JOIN school sch ON
s.school_id = sch.id
WHERE
s.is_deleted = 0
) stu_sch2 ON
date_range.date = DATE(stu_sch2.create_time)
GROUP BY
date_range.date
ORDER BY
date_range.date
2. 生成日期序列
需求需生成从2023-12-11
至2024-05-10
的日期序列。将此问题拆分为如下步骤:
生成数字序列;
DATE_ADD
函数将起始日期(2023-12-11
)与数字序列相加,得到日期序列。
2.1 生成数字序列
SELECT
seq
FROM
(
SELECT
@row := @row + 1 as seq
FROM
information_schema.columns,
(
SELECT
@row := -1
) r1
LIMIT
200
) sc
SQL解析:
information_schema.columns
是一个系统表,它包含MySQL服务器中所有数据库的所有表的列信息。在这里,它只是作为一个大表来提供足够的行以生成所需的200个整数。SELECT @row := -1
是一个子查询,它初始化一个名为@row
的变量,值为-1。@row := @row + 1 as seq
是一个表达式,它在每一行上都会执行。它将@row
的值增加1,并将结果赋值给seq
。LIMIT 200
限制结果集的大小为200行。
所以,这段SQL代码的结果是一个包含200行的表,每一行都有一个名为seq
的列,值从0到199。
2.2 生成日期序列
SELECT
DATE_ADD('2023-12-11', INTERVAL seq DAY) AS date
FROM
(
SELECT
seq
FROM
(
SELECT
@row := @row + 1 as seq
FROM
information_schema.columns,
(
SELECT
@row := -1
) r1
LIMIT
200
) s
) dates
WHERE
DATE_ADD(
'2023-12-11 00:00:00', INTERVAL seq DAY
) <= '2024-05-10 00:00:00'
SQL解析:
SELECT @row := @row + 1 as seq
:这是一个子查询,它初始化一个名为@row
的变量,值为-1,然后在每一行上将@row
的值增加1,并将结果赋值给seq
。FROM information_schema.columns, (SELECT @row := -1) r1
:这里使用了information_schema.columns
系统表来生成足够的行以生成所需的日期范围。(SELECT @row := -1) r1
是一个子查询,用于初始化@row
变量。LIMIT 200
:限制结果集的大小为200行,这意味着最多可以生成200个日期。DATE_ADD('2023-12-11', INTERVAL seq DAY) AS date
:使用DATE_ADD
函数和seq
值来生成新的日期。WHERE DATE_ADD('2023-12-11 00:00:00', INTERVAL seq DAY) <= '2024-05-10 00:00:00'
:这是一个过滤条件,只有满足条件的日期(即小于或等于'2024-05-10')才会被包含在结果集中。
3. 其他
3.1 COALESCE
COALESCE
是一个SQL函数,它返回参数列表中的第一个非NULL值。如果所有参数都是NULL,那么COALESCE
函数将返回NULL。
这是COALESCE
函数的基本语法:
COALESCE(expression1, expression2, ..., expression_n)
在这个语法中,expression1
、expression2
等是要测试的表达式。COALESCE
函数将从左到右评估这些表达式,返回第一个非NULL的表达式的值。
3.2 DATE
DATE函数是MySQL中的一个函数,用于从日期或日期时间值中提取日期部分。
这是DATE函数的基本语法:
DATE(expression)
在这个语法中,expression
是一个日期或日期时间表达式。