多聚合子查询关联报错求助：SQL语法错误排查与修正

原始订单退货数据 #

order_id    chef_name   order_returned
1001    Charles McBakey Y
1001    Sarah McCookin  N
1001    John McFry  N
1001    Charles McBakey N
1001    John McFry  N
1001    John McFry  Y
1001    John McFry  Y
1001    Sarah McCookin  N
1001    Charles McBakey N
1001    Sarah McCookin  N
1001    Charles McBakey Y
1001    Charles McBakey N
1001    Sarah McCookin  N
1001    John McFry  N
1001    Sarah McCookin  N
1001    Charles McBakey Y
1001    John McFry  N
1001    Sarah McCookin  Y
1001    John McFry  Y 

单独查询结果 #

查询总订单数 #

执行SQL：

select chef_name, count(chef_name) as cnt_total
from order_returns t
group by chef_name

返回结果：

"chef_name" "cnt_total"
"Sarah McCookin"    6
"Charles McBakey"   6
"John McFry"    7

查询退货订单数（标记为Y） #

执行SQL：

select chef_name, count(chef_name) as cnt_y
from order_returns 
where order_returned = 'Y'
group by chef_name

返回结果：

"chef_name" "cnt_y"
"Charles McBakey"   3
"John McFry"    3
"Sarah McCookin"    1

查询未退货订单数（标记为N） #

执行SQL：

select chef_name,count(chef_name) as cnt_n
from orders
where order_returned = 'N'
group by chef_name

返回结果：

"chef_name" "cnt_n"
"Charles McBakey"   3
"John McFry"    4
"Sarah McCookin"    5

关联查询报错 #

尝试用以下SQL关联三个查询结果：

select chef_name, count(chef_name) as cnt_total
from order_returns t
group by chef_name
join
(select chef_name, count(chef_name) as cnt_y
from order_returns 
where order_returned = 'Y'
group by chef_name) y
on t.chef_name = y.chef_name
join
(select chef_name,count(chef_name) as cnt_n
from order_returns
where order_returned = 'N'
group by chef_name) n
on y.chef_name = n.chef_name

错误提示：

ERROR: syntax error at or near "join" LINE 4: join.

解决方法 #

错误原因 #

SQL语句的语法顺序错误：GROUP BY子句必须放在JOIN操作之后，不能在关联表之前就进行分组。

推荐写法（条件聚合，高效简洁） #

不需要多次子查询关联，通过条件聚合一次扫描表即可得到所有需要的统计值：

select 
    chef_name,
    count(*) as cnt_total,
    sum(case when order_returned = 'Y' then 1 else 0 end) as cnt_y,
    sum(case when order_returned = 'N' then 1 else 0 end) as cnt_n
from order_returns
group by chef_name

子查询关联写法（调整顺序后） #

如果坚持用子查询关联，需调整语句顺序，先完成表关联，再执行分组：

select 
    t.chef_name,
    count(t.chef_name) as cnt_total,
    y.cnt_y,
    n.cnt_n
from order_returns t
join (
    select chef_name, count(chef_name) as cnt_y
    from order_returns 
    where order_returned = 'Y'
    group by chef_name
) y on t.chef_name = y.chef_name
join (
    select chef_name, count(chef_name) as cnt_n
    from order_returns
    where order_returned = 'N'
    group by chef_name
) n on t.chef_name = n.chef_name
group by t.chef_name, y.cnt_y, n.cnt_n

注：此写法需要多次扫描表，效率低于条件聚合。

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