SQL报错：HAVING子句应返回BOOL类型却返回INT64问题排查

错误根源 #

报错提示HAVING clause should return type BOOL, but returns INT64的核心原因：

• HAVING子句要求返回**布尔值（TRUE/FALSE）**来筛选分组结果，但你写的count(orders.order_id >0)是聚合函数，返回的是整数（统计分组内满足orders.order_id >0的行数），类型不匹配触发报错。
• 同时这个写法逻辑错误：左连接后，无订单的仓库对应的orders.order_id为NULL，orders.order_id >0会返回FALSE，但count函数会把FALSE视为非NULL值计入统计，最终结果等于分组总行数，完全达不到筛选有订单仓库的目的。

修复方案 #

针对筛选有订单仓库的需求，将HAVING子句改为返回布尔值的判断，两种简单写法如下：

写法1：使用已定义的聚合字段

直接引用SELECT中定义的number_of_orders字段判断：

select 
    warehouse.warehouse_id,
    CONCAT(warehouse.state, ': ', warehouse.warehouse_alias) as warehouse_name,
    count(orders.order_id) as number_of_orders,
    (select count(*) from warehouse_orders.orders orders) as total_orders,
    case
      when count(orders.order_id)/(select count(*) from warehouse_orders.orders orders) <= 0.20
      then "fulfilled 0-20% of Orders"
      when count(orders.order_id)/(select count(*) from warehouse_orders.orders orders) >= 0.20
      AND count(orders.order_id)/(select count(*) from warehouse_orders.orders orders) <= 0.60
      then "fulfilled 21-60% of Orders"
    else "fulfilled more than 60% of orders"
    END AS fulfillment_summary
from warehouse_orders.warehouse warehouse
left join warehouse_orders.orders orders
  ON orders.warehouse_id = warehouse.warehouse_id
group by
  warehouse.warehouse_id,
  warehouse_name
HAVING
  number_of_orders > 0

写法2：直接在HAVING中写聚合判断

select 
    warehouse.warehouse_id,
    CONCAT(warehouse.state, ': ', warehouse.warehouse_alias) as warehouse_name,
    count(orders.order_id) as number_of_orders,
    (select count(*) from warehouse_orders.orders orders) as total_orders,
    case
      when count(orders.order_id)/(select count(*) from warehouse_orders.orders orders) <= 0.20
      then "fulfilled 0-20% of Orders"
      when count(orders.order_id)/(select count(*) from warehouse_orders.orders orders) >= 0.20
      AND count(orders.order_id)/(select count(*) from warehouse_orders.orders orders) <= 0.60
      then "fulfilled 21-60% of Orders"
    else "fulfilled more than 60% of orders"
    END AS fulfillment_summary
from warehouse_orders.warehouse warehouse
left join warehouse_orders.orders orders
  ON orders.warehouse_id = warehouse.warehouse_id
group by
  warehouse.warehouse_id,
  warehouse_name
HAVING
  count(orders.order_id) > 0

额外优化建议 #

原SQL中多次重复执行(select count(*) from warehouse_orders.orders orders)子查询，会降低查询效率，可通过交叉连接提前计算总订单数：

select 
    warehouse.warehouse_id,
    CONCAT(warehouse.state, ': ', warehouse.warehouse_alias) as warehouse_name,
    count(orders.order_id) as number_of_orders,
    t.total_orders,
    case
      when count(orders.order_id)/t.total_orders <= 0.20
      then "fulfilled 0-20% of Orders"
      when count(orders.order_id)/t.total_orders >= 0.20
      AND count(orders.order_id)/t.total_orders <= 0.60
      then "fulfilled 21-60% of Orders"
    else "fulfilled more than 60% of orders"
    END AS fulfillment_summary
from warehouse_orders.warehouse warehouse
left join warehouse_orders.orders orders
  ON orders.warehouse_id = warehouse.warehouse_id
cross join (select count(*) as total_orders from warehouse_orders.orders) t
group by
  warehouse.warehouse_id,
  warehouse_name,
  t.total_orders
HAVING
  number_of_orders > 0

内容的提问来源于stack exchange，提问作者JS- blueslowtime