执行带CTE的SQL关联查询提示表test.b不存在问题排查

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+-------------+---------+

WITH temp AS 
(SELECT seller_id, SUM(price) AS sum_price FROM Sales GROUP BY seller_id)
SELECT A.seller_id FROM temp A, temp B
WHERE A.sum_price >= ALL(SELECT sum_price FROM B);

Table 'test.b' doesn't exist

这个报错是两个写法问题共同导致的：

• 无意义的笛卡尔积：代码里写FROM temp A, temp B是对CTE临时表temp做笛卡尔积连接，这个操作对「查询销售额最高的卖家」这个需求完全没有帮助，只会生成冗余的重复数据。
• 子查询表引用非法：>= ALL 后的子查询里写了FROM B，SQL执行时会把B当做当前库下的独立实体表去查找，不会识别成外层FROM里给temp定义的别名B。SQL语法规则里，只有子查询的WHERE、SELECT等判断/取值位置可以引用外层表的字段，子查询自己的FROM子句必须引用真实存在的表、视图、CTE，不能直接用外层的表别名，库中没有叫B的实体表自然会报表不存在的错误。

去掉多余的笛卡尔积写法，直接在ALL子查询中引用CTE临时表temp即可，该写法可以返回所有销售额并列最高的卖家：

WITH temp AS 
(SELECT seller_id, SUM(price) AS sum_price FROM Sales GROUP BY seller_id)
SELECT seller_id FROM temp
WHERE sum_price >= ALL(SELECT sum_price FROM temp);

如果确定不存在销售额并列最高的场景，也可以用排序取首条的写法，大数据量下性能更优：

WITH temp AS 
(SELECT seller_id, SUM(price) AS sum_price FROM Sales GROUP BY seller_id)
SELECT seller_id FROM temp
ORDER BY sum_price DESC
LIMIT 1;

内容的提问来源于stack exchange，提问作者Matt Jackson