mysql - GROUP_CONCAT 不适用于左连接

Question

此查询显示错误: #1054 - “on 子句”中的未知列“sp.spot_id”

查询:

SELECT product.*,sp.sp_name FROM `product` 
     left join spot_selling on product.product_id=spot_selling.product_id 
      AND spot_selling.end_time >= now() 
      AND spot_selling.start_time <= now() 
      AND spot_selling.status='1' 
    left join(select GROUP_CONCAT(s.name SEPARATOR ',') as sp_name 
from spot s group by s.spot_id) sp on sp.spot_id=spot_selling.spot_id 
WHERE product.user_id='26' AND product.status!='6'

Answer 1

我认为你只需要子查询中的 spot_id ，所以有一些东西可以加入:

SELECT product.*, sp.sp_name
FROM `product` left join
      spot_selling
      on product.product_id=spot_selling.product_id AND
         spot_selling.end_time >= now() AND
         spot_selling.start_time <= now() AND
         spot_selling.status = '1' left join
      (select spot_id, GROUP_CONCAT(s.name SEPARATOR ',') as sp_name 
--------------^
       from spot s
       group by s.spot_id
      ) sp
      on sp.spot_id = spot_selling.spot_id 
WHERE product.user_id = '26' AND product.status <> '6'