上一章我们把多张表拼成了一张宽表。但真实问题很少一层 JOIN 就能说完:
“哪些城市高于平均”“每个用户属于哪一档”“先算月再算环比”——
都要在一条 SQL 里分步骤完成。
子查询和 CTE 就是分步骤的工具。它们难的不是语法,而是看清形状:
中间结果是一行一列、一列多行,还是多行多列?形状错了,SQL 要么报错,
要么悄悄返回空集。
本章前一半讲五条规则,其中 NOT IN 撞上 NULL 会让 565 笔游客订单凭空消失;
后一半用 8 步实验,把 5 层嵌套拆成 3 段 CTE。
子查询的定义很朴素:把一条 SELECT 的结果,当成另一条 SELECT 的输入。 既然是输入,就要先看清它长什么样——这就是形状。 形状决定它能放在 FROM、WHERE,还是只能当一个值用。
按返回结果的形状,子查询只有三类。这里的“行”“列”是字面意思: 单独把子查询跑一遍,看到几行几列,它就是什么形状。
| 形状 | 返回什么 | 能放在哪 | 典型写法 |
|---|---|---|---|
| 标量 | 1 行 1 列,本质是一个值 | SELECT 列表、WHERE、HAVING | = (SELECT AVG(...)) |
| 列 | 1 列 N 行,是一串值 | 只能配 IN / NOT IN | IN (SELECT city ...) |
| 表 | N 行 M 列,是一张临时表 | 只能放在 FROM 后面 | FROM (SELECT ...) t |
标量:一个值,可以直接参与比较和运算。本库已支付订单的平均实付是 10,077.71 元,用它筛出高于平均的订单:
SELECT COUNT(*) AS n, ROUND(SUM(pay_amount), 2) AS gmv FROM orders WHERE status = '已支付' AND pay_amount > (SELECT AVG(pay_amount) FROM orders WHERE status = '已支付');
n | gmv 6777 | 112396535.12
15,615 笔已支付订单里,6,777 笔高于平均,占 43.4%,却贡献 71.4% 的 GMV。 金额分布右偏,少数大单把均值拉高,所以“高于平均”的人不到一半。
列:一串值,只能喂给 IN。先找出注册用户不少于 250 人的城市,再拿这串城市去筛订单:
SELECT city, COUNT(*) AS users FROM users GROUP BY city HAVING COUNT(*) >= 250 ORDER BY users DESC;
city | users 上海 | 288 西安 | 257 北京 | 256 武汉 | 251 成都 | 250
SELECT order_id, city, ROUND(pay_amount, 2) AS amount FROM orders WHERE status = '已支付' AND city IN (SELECT city FROM users GROUP BY city HAVING COUNT(*) >= 250) ORDER BY pay_amount DESC LIMIT 3;
order_id | city | amount O23011603143 | 北京 | 43168.96 O23030518411 | 成都 | 42253.75 O23082019847 | 上海 | 41546.31
表:一张临时表,放在 FROM 里。下面的派生表把 8 个城市压成 8 行,再排序取前 3:
SELECT city, gmv
FROM (
SELECT city, ROUND(SUM(pay_amount), 2) AS gmv
FROM orders
WHERE status = '已支付'
GROUP BY city
) t
ORDER BY gmv DESC
LIMIT 3;
city | gmv 杭州 | 20508489.75 武汉 | 20318374.79 上海 | 20009620.63
标量当值用,列配 IN,表进 FROM,三者不能互换。
列子查询放到 = 右边,是拿一个值和一串值比大小;
表子查询放进 WHERE,是拿一行记录和条件比,都不成立。
如果不这样会怎样:在 SQLite 里,标量位置放了返回多行的子查询,它不报错,
而是悄悄取第一行。本库执行 SELECT (SELECT city FROM users) 返回“深圳”——
一个和业务无关的城市。MySQL、PostgreSQL 会直接报错,SQLite 不会,这一点要格外小心。
形状讲“返回什么”,相关与否讲“怎么算”。判断只有一句话: 子查询里有没有引用外层的列。没有是非相关,有是相关。
上面的 AVG(pay_amount) 是非相关的:只依赖 orders 自己,
和外面正在比较的那一行无关,可以先算成常数 10,077.71。
相关子查询则引用了外层。下面判断“这笔订单是否高于所在城市的平均值”,
o.city 来自外层:
SELECT COUNT(*) AS n, ROUND(SUM(pay_amount), 2) AS gmv
FROM orders o
WHERE o.status = '已支付'
AND o.pay_amount > (
SELECT AVG(o2.pay_amount)
FROM orders o2
WHERE o2.status = '已支付' AND o2.city = o.city
);
n | gmv 6771 | 112331577.36
概念上,外层每取一行,内层就按这一行的城市重算一次平均。20,000 行仍是瞬间, 外层几百万行就是灾难——第 17 章会用 EXPLAIN 看计划。能改写成 JOIN 或窗口函数的,优先改写。
非相关只算一次,结果当常量;相关依赖外层当前行,逻辑上要执行 N 次。 写之前问一句:“换个外层行,答案会变吗?”会变就是相关。
如果不这样会怎样:把相关当成非相关,你会以为两个口径结果一样。 全局均值是 6,777 单,城市均值是 6,771 单,只差 6 单, 但具体是哪些订单并不相同。用总数判断口径,是最容易翻车的地方。
这是本章唯一一个“不报错、但结果完全错”的知识点。 先接受一件事:SQL 的布尔值有三种,TRUE、FALSE 和 UNKNOWN。 WHERE 只保留 TRUE 的行,UNKNOWN 和 FALSE 一样被丢掉。
本库 orders 表有 565 笔游客订单,user_id 是 NULL。现在业务问:
“有多少笔订单的下单人不在用户表里?”直觉写法是 NOT IN:
SELECT COUNT(*) AS n FROM orders WHERE user_id NOT IN (SELECT user_id FROM users);
n 0
结果是 0。就此写下“所有下单人都是注册用户”就错了:查一句
SELECT COUNT(*) FROM orders WHERE user_id IS NULL,答案是 565。
它们不是“在用户表里”,而是根本没参与比较。
原因在于,x NOT IN (a, b, c) 会展开成
x <> a AND x <> b AND x <> c。x 是 NULL 时,
NULL <> 'U000001' 得 UNKNOWN,而 UNKNOWN AND TRUE 仍是 UNKNOWN,
整条链断掉,这一行被丢弃。
| x | x NOT IN ('U000001','U000002') | WHERE 是否保留 |
|---|---|---|
| U000007 | TRUE | 保留 |
| U000001 | FALSE | 丢弃 |
| NULL | UNKNOWN | 丢弃 |
更隐蔽的是反向版本:NULL 藏在子查询的列表里。下面想数“不是北京用户下的单”, 子查询的 user_id 里混着 NULL,于是整条 NOT IN 对任何一行都不成立:
-- 错误:子查询结果里含 565 个 NULL SELECT COUNT(*) AS n FROM orders WHERE user_id NOT IN (SELECT user_id FROM orders WHERE city = '北京');
n 0
-- 正确:把 NULL 挡在子查询外面
SELECT COUNT(*) AS n
FROM orders
WHERE user_id NOT IN (
SELECT user_id FROM orders WHERE city = '北京' AND user_id IS NOT NULL
);
n 5085
0 和 5,085,差五千多行,数据库一声不吭。结论越吓人,越要先怀疑 NULL。
NULL 不是 0,也不是空字符串,它是“未知”。拿值和“未知”比较,答案只能是 UNKNOWN, 而 WHERE 只认 TRUE。NULL 一旦进入 NOT IN 的比较链,这一行就留不下来。
如果不这样会怎样:本库 565 笔游客订单、合计 4,274,401.92 元 GMV, 会在“排除已注册用户”这类查询里整体消失,占全站 2.72%。 对账时总差两百多万,却找不到是哪一批单。
NOT EXISTS 就别用 NOT IN,
前者逐行判断存在性,NULL 不会污染结果;② 非用 NOT IN 不可,就在子查询里加
WHERE user_id IS NOT NULL。两种写法在本库都返回 565。
子查询是“套娃”,层数一多就要从里往外翻。CTE(公用表表达式) 换了个方向:从上往下写,一层一个名字。语法是在 SELECT 前加 WITH:
WITH paid AS (
SELECT channel, order_date, pay_amount
FROM orders
WHERE status = '已支付'
),
monthly AS (
SELECT channel,
substr(order_date, 1, 7) AS ym,
SUM(pay_amount) AS gmv
FROM paid
GROUP BY channel, ym
)
SELECT channel,
ROUND(SUM(gmv), 2) AS gmv,
ROUND(AVG(gmv), 2) AS avg_month
FROM monthly
GROUP BY channel
ORDER BY gmv DESC;
channel | gmv | avg_month 自然搜索 | 63215624.36 | 3511979.13 信息流 | 47042514.15 | 2613473.01 朋友推荐 | 32164193.80 | 1786899.66 线下 | 14941047.83 | 830058.21
paid 是“已支付订单”,monthly 是“渠道 × 月的 GMV”,
最后一步在月粒度上再汇总。自然搜索月均 3,511,979.13、信息流 2,613,473.01,
就是各自总额除以 18。
CTE 有四条性质,必须记清楚:
| 性质 | 含义 |
|---|---|
| 只活在一条语句里 | 查询结束,名字就没了。下一条 SELECT 不能再用。 |
| 只能向后引用 | 后面的 CTE 可以引用前面的,前面的不能引用后面的。 |
| 不是临时表 | 不落地、不建索引。SQLite 可能把它内联展开,也可能物化,别假设它只算一次。 |
| 可以多次引用 | 同一个名字在 FROM 和子查询里各用一次,是允许的,也是 CTE 比派生表方便的地方。 |
一个 CTE 只做一件事,名字要能读出业务含义:paid、monthly、
city_agg。读到 FROM 那一行,不用翻回上面就知道粒度。
这就是“流水线”和“套娃”的区别。
如果不这样会怎样:把 CTE 当临时表,写 8 个互相引用的 CTE,名字全叫
t1、t2、t3。查询能跑,但没人能改,
三天后你自己也得从头读一遍。
CASE 有两种写法,区别只在判断方式:
| 写法 | 形式 | 适用场景 |
|---|---|---|
| 简单 CASE | CASE 列 WHEN 值 THEN 结果 ... END | 只做等值匹配,比如会员等级映射 |
| 搜索 CASE | CASE WHEN 条件 THEN 结果 ... END | 范围、组合条件,比如按累计消费分层 |
SELECT member_level,
CASE member_level
WHEN '黑卡' THEN 'S'
WHEN '金卡' THEN 'A'
WHEN '银卡' THEN 'B'
ELSE 'C'
END AS grade,
COUNT(*) AS users
FROM users
GROUP BY member_level
ORDER BY users DESC;
member_level | grade | users 普通 | C | 1169 银卡 | B | 552 金卡 | A | 196 黑卡 | S | 83
搜索 CASE 更常用。按累计消费给 2,000 个用户分四层,阈值分别是 10 万、7.5 万、5 万:
SELECT
CASE WHEN spend >= 100000 THEN 'A 高价值'
WHEN spend >= 75000 THEN 'B 成长'
WHEN spend >= 50000 THEN 'C 潜力'
ELSE 'D 长尾' END AS tier,
COUNT(*) AS users,
ROUND(SUM(spend), 2) AS total
FROM (
SELECT user_id, SUM(pay_amount) AS spend
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
GROUP BY user_id
) t
GROUP BY tier
ORDER BY tier;
tier | users | total A 高价值 | 482 | 58830222.80 B 成长 | 502 | 43638815.67 C 潜力 | 565 | 35136501.47 D 长尾 | 451 | 15483438.28
| 累计消费 | 第 1 个分支 ≥10 万 | 第 2 个分支 ≥7.5 万 | 第 3 个分支 ≥5 万 | 命中 |
|---|---|---|---|---|
| 120,000 | TRUE | 不再判断 | 不再判断 | A 高价值 |
| 80,000 | FALSE | TRUE | 不再判断 | B 成长 |
| 60,000 | FALSE | FALSE | TRUE | C 潜力 |
| 30,000 | FALSE | FALSE | FALSE | D 长尾(ELSE) |
“第一个命中就停”意味着顺序就是逻辑。把条件从小到大写,会得到完全不同的结果:
-- 错误:小阈值写在前面
SELECT CASE WHEN spend >= 50000 THEN 'C 潜力'
WHEN spend >= 75000 THEN 'B 成长'
WHEN spend >= 100000 THEN 'A 高价值'
ELSE 'D 长尾' END AS tier,
COUNT(*) AS users
FROM (
SELECT user_id, SUM(pay_amount) AS spend
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
GROUP BY user_id
) t
GROUP BY tier
ORDER BY tier;
tier | users C 潜力 | 1549 D 长尾 | 451
所有 5 万以上的用户都在第一个分支被拦下,A、B 两层一个人都进不去—— 四层塌成两层,SQL 不会给任何提示。
CASE 逐行判断,遇到第一个 TRUE 就返回。范围判断必须按“最严格 → 最宽松”排列, 最后用 ELSE 兜底。不写 ELSE 时返回 NULL,GROUP BY 会把 NULL 单独算成一组, 报表上多出一行空层级。
如果不这样会怎样:顺序写反,A 层 482 人、B 层 502 人全部消失, 1,549 人被贴上“潜力”标签。拿去做投放,预算全打给中间层, 真正的高价值用户一个都没覆盖到。
这一节不讲新语法,只练一件事:拿到一段看不懂的嵌套,怎么在 5 分钟内拆开。 业务问题是——“哪些城市的高价值用户最多?占本地用户多少?”
原始写法,5 个 SELECT 套在一起:
SELECT city, high_users, total_users, -- 第 5 层
ROUND(high_users * 100.0 / total_users, 2) AS pct
FROM (
SELECT city, -- 第 4 层
SUM(CASE WHEN tier = '高价值' THEN 1 ELSE 0 END) AS high_users,
COUNT(*) AS total_users
FROM (
SELECT u.city AS city, t.tier AS tier -- 第 3 层
FROM (
SELECT s.user_id, -- 第 2 层
CASE WHEN s.spend >= 100000 THEN '高价值' ELSE '普通' END AS tier
FROM (
SELECT user_id, SUM(pay_amount) AS spend -- 第 1 层
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
GROUP BY user_id
) s
) t
JOIN users u ON u.user_id = t.user_id
) ut
GROUP BY city
) c
ORDER BY high_users DESC
LIMIT 3;
city | high_users | total_users | pct 北京 | 68 | 256 | 26.56 成都 | 67 | 250 | 26.80 广州 | 63 | 243 | 25.93
直接读容易晕。换个方法:从最内层开始,每层只问三个问题—— 输入是哪张表?做了什么动作?输出几行?填进下面这张表,嵌套就成了流水线。
| 层 | 输入 | 动作 | 输出行数 | 该起的名字 |
|---|---|---|---|---|
| 第 1 层 | orders | 按 user_id 汇总实付 | 2,000 | user_spend |
| 第 2 层 | 第 1 层 | CASE 打标签 | 2,000 | tagged |
| 第 3 层 | 第 2 层 + users | 按城市 JOIN,取城市名 | 2,000 | —(并入 city_agg) |
| 第 4 层 | 第 3 层 | 按城市计数 | 8 | city_agg |
| 第 5 层 | 第 4 层 | 算占比、排序、取前 3 | 3 | 主查询 |
第 2、3 层都是 2,000 行:打标签和 JOIN users 都是“一行对一行”,不改变粒度。 真正把行数压到 8 的是第 4 层的 GROUP BY。看清这一点,就知道哪一步可能翻倍—— 这是第 13 章粒度意识的延续。
按表里的名字重写,5 层变成 3 段 CTE + 1 个主查询:
WITH user_spend AS (
SELECT user_id, SUM(pay_amount) AS spend
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
GROUP BY user_id
),
tagged AS (
SELECT s.user_id, s.spend,
CASE WHEN s.spend >= 100000 THEN '高价值' ELSE '普通' END AS tier
FROM user_spend s
),
city_agg AS (
SELECT u.city AS city,
SUM(CASE WHEN t.tier = '高价值' THEN 1 ELSE 0 END) AS high_users,
COUNT(*) AS total_users
FROM tagged t
JOIN users u ON u.user_id = t.user_id
GROUP BY u.city
)
SELECT city, high_users, total_users,
ROUND(high_users * 100.0 / total_users, 2) AS pct
FROM city_agg
ORDER BY high_users DESC
LIMIT 3;
city | high_users | total_users | pct 北京 | 68 | 256 | 26.56 成都 | 67 | 250 | 26.80 广州 | 63 | 243 | 25.93
结果一模一样。改写带来三个好处:能单独验证每一段(拎出来跑,看行数对不对)、 能复用中间结果(city_agg 可以再 JOIN 城市 GMV)、 能一眼看出粒度在哪一步变化。
下面 8 步都在 qingning.db 上完成,每步按四段写,“为什么”对应 14.1 的规则。
做完之后,你应该能写出“先聚合、再筛选、再分层”的任意组合。
SELECT COUNT(*) AS n, ROUND(SUM(pay_amount), 2) AS gmv FROM orders WHERE status = '已支付' AND pay_amount > (SELECT AVG(pay_amount) FROM orders WHERE status = '已支付');
结果接近 15,615:子查询里的 status 条件漏了,平均值被未支付订单拉低。
结果接近 7,808:用了 >= 且外层没筛状态,两个口径混在一起了。
o.city,外层每行都要重算。这是相关与非相关的分水岭。SELECT COUNT(*) AS n, ROUND(SUM(pay_amount), 2) AS gmv
FROM orders o
WHERE o.status = '已支付'
AND o.pay_amount > (
SELECT AVG(o2.pay_amount)
FROM orders o2
WHERE o2.status = '已支付' AND o2.city = o.city
);
再取 3 笔最大的,把“本单金额”和“同城均值”并排看:
SELECT o.order_id, o.city,
ROUND(o.pay_amount, 2) AS amount,
ROUND((SELECT AVG(o2.pay_amount) FROM orders o2
WHERE o2.status = '已支付' AND o2.city = o.city), 2) AS city_avg
FROM orders o
WHERE o.status = '已支付'
AND o.pay_amount > (SELECT AVG(o2.pay_amount) FROM orders o2
WHERE o2.status = '已支付' AND o2.city = o.city)
ORDER BY o.pay_amount DESC
LIMIT 3;
八个城市客单价都在 9,962–10,267 之间,非常接近,所以两种口径的人数几乎一样。但两批订单不是同一批:一笔 10,100 元的上海订单,全国口径下高于 10,077.71,同城口径下低于 10,266.61。
结论:单量接近不代表口径等价,要核对订单号。
SELECT order_id, city, ROUND(pay_amount, 2) AS amount
FROM orders
WHERE status = '已支付'
AND city IN (
SELECT city FROM users
GROUP BY city
HAVING COUNT(*) >= 250
)
ORDER BY pay_amount DESC
LIMIT 3;
把 HAVING 写成 WHERE COUNT(*) >= 250:报 misuse of aggregate: count()。聚合条件只能放 HAVING。
SELECT city, ROUND(gmv, 2) AS gmv
FROM (
SELECT city, SUM(pay_amount) AS gmv
FROM orders
WHERE status = '已支付'
GROUP BY city
) t
WHERE gmv > (SELECT AVG(gmv) FROM (
SELECT SUM(pay_amount) AS gmv
FROM orders
WHERE status = '已支付'
GROUP BY city
) x)
ORDER BY gmv DESC;
深圳 19,691,841.98 只比平均线高 21,419.46 元,约 0.11%,但确实在线上。凭感觉数“前四名”会漏掉它。
另外,8 城平均 19,670,422.52 和全站客单价 10,077.71 不是一回事。
SELECT
CASE WHEN spend >= 100000 THEN 'A 高价值'
WHEN spend >= 75000 THEN 'B 成长'
WHEN spend >= 50000 THEN 'C 潜力'
ELSE 'D 长尾' END AS tier,
COUNT(*) AS users,
ROUND(SUM(spend), 2) AS total
FROM (
SELECT user_id, SUM(pay_amount) AS spend
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
GROUP BY user_id
) t
GROUP BY tier
ORDER BY tier;
四层合计 153,088,978.22,比 GMV 少 4,274,401.92。差额正是游客订单,被 user_id IS NOT NULL 排除了,第 8 步会对账。
WITH city_gmv AS (
SELECT city, SUM(pay_amount) AS gmv
FROM orders
WHERE status = '已支付'
GROUP BY city
)
SELECT city, ROUND(gmv, 2) AS gmv
FROM city_gmv
WHERE gmv > (SELECT AVG(gmv) FROM city_gmv)
ORDER BY gmv DESC;
这里 city_gmv 用了两次:FROM 一次,子查询求平均一次。派生表没有名字,只能把整段 SQL 再抄一遍。
no such table: city_gmv:CTE 只在同一条语句里有效,分号后就失效了。
把 avg_gmv 写在前面:CTE 不能引用后面的,调整顺序即可。
SELECT channel, ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-01' THEN pay_amount ELSE 0 END), 2) AS m1, ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-02' THEN pay_amount ELSE 0 END), 2) AS m2, ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-03' THEN pay_amount ELSE 0 END), 2) AS m3, ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-04' THEN pay_amount ELSE 0 END), 2) AS m4, ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-05' THEN pay_amount ELSE 0 END), 2) AS m5, ROUND(SUM(CASE WHEN substr(order_date,1,7) = '2024-06' THEN pay_amount ELSE 0 END), 2) AS m6, ROUND(SUM(pay_amount), 2) AS total FROM orders WHERE status = '已支付' AND order_date >= '2024-01-01' GROUP BY channel ORDER BY total DESC;
| 渠道 | 2024-01 | 2024-02 | |
|---|---|---|---|
| 长表 | 自然搜索 | 3,686,170.74 | — |
| 长表 | 自然搜索 | — | 3,288,474.41 |
| ↓ | 月份从“行”搬到“列” | ||
| 宽表 | 自然搜索 | 3,686,170.74 | 3,288,474.41 |
某个月整列是 0:日期写成 '2024-1'。库里是 2024-01,字符串比较必须补零。
合计比 5,357 万大:忘了加 order_date >= '2024-01-01',把 2023 年的数据也算进了各列。
user_id IS NOT NULL,主动排除了 565 笔游客订单。凡是被 WHERE 排除的行,都必须能解释——这是第 6 章“每个动作留痕”的 SQL 版。SELECT '登录用户累计消费' AS item, ROUND(SUM(spend), 2) AS amount
FROM (
SELECT user_id, SUM(pay_amount) AS spend
FROM orders
WHERE status = '已支付' AND user_id IS NOT NULL
GROUP BY user_id
) t
UNION ALL
SELECT '游客订单', ROUND(SUM(pay_amount), 2)
FROM orders
WHERE status = '已支付' AND user_id IS NULL
UNION ALL
SELECT 'GMV 合计', ROUND(SUM(pay_amount), 2)
FROM orders
WHERE status = '已支付';
565 是游客订单总数,其中只有 439 笔已支付。GMV 只算已支付,所以游客贡献是 439 笔、4,274,401.92 元。
先填三个数,再做题。任何一个对不上,都说明某一层的口径漏了行。
不看正文,凭第一反应选。选完立刻出解析。
= 右边时,必须是什么形状?= 是标量比较,右边必须是一个值,即 1 行 1 列。1 列 N 行配 IN,N 行 M 列进 FROM。WHERE user_id NOT IN (SELECT user_id FROM orders WHERE city = '北京') 返回 0 行,最可能的原因是什么?AND user_id IS NOT NULL 后是 5,085。>= 50000 写在 >= 100000 前面,会发生什么?按“现象 → 原因 → 解决”整理。报错大多指向同一个根因:形状或位置。
| 现象 | 原因 | 解决 |
|---|---|---|
| 子查询返回多行,SQLite 却不报错 | 标量位置放了列子查询,SQLite 取第一行 | 加聚合或 LIMIT 1;确认业务上真的只要一行 |
| NOT IN 返回 0 行 | 比较链里有 NULL(左值或子查询结果) | 改 NOT EXISTS,或在子查询加 IS NOT NULL |
near "select": syntax error | 子查询没加括号,写成了 IN SELECT ... | 补上括号:IN (SELECT ...) |
| misuse of aggregate: sum() | 把聚合函数写进了 WHERE | 聚合条件放 HAVING,或先做成派生表再筛 |
| CASE 分层少了几档 | 阈值顺序反了,大范围写在了前面 | 按从大到小重排,最后加 ELSE 兜底 |
| no such table: city_gmv | CTE 只在一条语句内有效,语句已结束 | 把后续查询并进同一条 WITH 语句 |
| 行转列出现整列 0 | 月份字符串没补零(2024-1 ≠ 2024-01) | 统一用 substr(order_date,1,7) 生成月份 |
| 分层金额加总小于 GMV | 过滤掉了 user_id 为 NULL 的游客订单 | 差额单独列一行,或把游客作为独立分层 |
1. 把 GROUP BY city 的结果放进 HAVING,再和 (SELECT AVG(pay_amount) FROM orders WHERE status='已支付') 比。WHERE 在 GROUP BY 之前执行,那时还没有“城市客单价”这个值,写 AVG 会报 misuse of aggregate。
2. A 档 4,353 单 / 11,475,931.43 / 2,636.33;B 档 4,391 单 / 32,546,938.77 / 7,412.19;C 档 5,372 单 / 76,205,447.30 / 14,185.68;D 档 1,499 单 / 37,135,062.64 / 24,773.22。四档合计 15,615 单、157,363,380.14。
3. 杭州(GMV 第 1,高价值 60 人排第 6)、武汉(第 2 / 47 人排第 8)、上海(第 3 / 61 人排第 5)三个城市符合。广州 GMV 第 4、高价值 63 人排第 3,不在答案里。
4. 朋友推荐,3 月 2,107,458.78 对 2 月 1,636,665.91,环比 +28.77%;同期自然搜索 −0.81%、信息流 +14.11%、线下 −12.45%。
RANK() OVER (ORDER BY SUM(pay_amount) DESC)
直接给城市排名,还能顺手算出占比、累计和环比——不用再套娃。