MariaDB子查询WHERE列不存在及COUNT()分组统计异常求助

问题原因 #

你遇到的Unknown column 'table_buyer.buyer_id' in 'where clause'错误，是因为多层嵌套子查询的作用域限制：最内层的子查询无法直接引用最外层table_buyer表的字段，中间的派生表把作用域隔离了，导致数据库识别不到这个字段。

两种正确的查询写法 #

写法一：先聚合水果表再关联

先对table_fruit按买家ID和水果名称分组，得到每个买家的不同水果列表，再和买家表关联统计数量：

SELECT 
    b.buyer_name,
    COUNT(f.fruit_name) AS group_of_fruit
FROM table_buyer b
LEFT JOIN (
    SELECT buyer_id, fruit_name 
    FROM table_fruit 
    GROUP BY buyer_id, fruit_name
) f ON b.buyer_id = f.buyer_id
GROUP BY b.buyer_id, b.buyer_name;

写法二：用COUNT(DISTINCT)直接统计

这种方式更简洁，通过COUNT(DISTINCT)直接计算每个买家的不同水果种类数：

SELECT 
    b.buyer_name,
    COUNT(DISTINCT f.fruit_name) AS group_of_fruit
FROM table_buyer b
LEFT JOIN table_fruit f ON b.buyer_id = f.buyer_id
GROUP BY b.buyer_id, b.buyer_name;

执行结果 #

两种写法都会返回你期望的结果：

buyer_name | group_of_fruit
john       | 2
mike       | 1

原查询的问题补充 #

原查询的嵌套逻辑冗余，而且因为作用域问题导致字段不可见。即使强行解决作用域（比如把外层字段传递到中间层），也远不如上面两种写法高效直观。

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