如何在MySQL左连接子查询中传递user.phone_number变量？

问题原因 #

你遇到的1054错误是因为子查询无法直接访问外部查询的表字段。原查询中左连接的子查询里使用了phone_number=user.phone_number，但这个子查询是独立执行的，看不到外部的user表，所以提示找不到user.phone_number字段。

解决方案 #

方法1：使用窗口函数筛选单条数据（推荐，MySQL 8.0+支持） #

通过ROW_NUMBER()窗口函数，先给每个手机号对应的order_botorder记录按id倒序排序，然后只保留每条手机号的第一条数据，再和user表关联：

select
    user.id as id,
    user.updated_at as updated_at,
    if(user.name is not null, user.name, "No Name") as name,
    user.phone_number as phone_number,
    user.created_at as member_since,
    sum(if(offer_item.card_type="FIXED",offer.list_price,offer_item.custom_value)) as total_card_value,
    sum(offer_item.redeemed_amount) as redeemed_amount,
    sum(if(offer_item.is_complete=0, 1, 0)) as active_gift_card,
    bot_order.created_at as last_bot_order_time
from account_firstuser as user
LEFT JOIN offer_offerorder as offer_order ON user.phone_number = offer_order.phone_number
LEFT JOIN offer_offeritem as offer_item ON offer_order.id = offer_item.offer_order_id
LEFT JOIN offer_offer as offer ON offer_item.offer_id = offer.id
LEFT JOIN (
    select 
        bo.*,
        ROW_NUMBER() OVER (PARTITION BY bo.phone_number ORDER BY bo.id DESC) as rn
    from order_botorder bo
    where bo.store_id=1 and bo.is_pos=0 and bo.is_submitted=1
) bot_order ON bot_order.phone_number = user.phone_number AND bot_order.rn = 1
where
    user.store_id = 1 and
    user.phone_number LIKE '%%' and
    user.is_invalid = 0 and
    user.is_blocked = 0
group by user.id, user.phone_number, user.updated_at, user.name, user.created_at, bot_order.created_at
order by user.created_at desc 
limit 10 offset 0

方法2：兼容低版本MySQL（无窗口函数） #

如果你的MySQL版本低于8.0，可以用关联子查询直接获取每个用户的最新bot_order记录：

select
    user.id as id,
    user.updated_at as updated_at,
    if(user.name is not null, user.name, "No Name") as name,
    user.phone_number as phone_number,
    user.created_at as member_since,
    sum(if(offer_item.card_type="FIXED",offer.list_price,offer_item.custom_value)) as total_card_value,
    sum(offer_item.redeemed_amount) as redeemed_amount,
    sum(if(offer_item.is_complete=0, 1, 0)) as active_gift_card,
    (select bo.created_at 
     from order_botorder bo
     where bo.store_id=1 and bo.is_pos=0 and bo.is_submitted=1 and bo.phone_number=user.phone_number
     order by bo.id desc limit 1) as last_bot_order_time
from account_firstuser as user
LEFT JOIN offer_offerorder as offer_order ON user.phone_number = offer_order.phone_number
LEFT JOIN offer_offeritem as offer_item ON offer_order.id = offer_item.offer_order_id
LEFT JOIN offer_offer as offer ON offer_item.offer_id = offer.id
where
    user.store_id = 1 and
    user.phone_number LIKE '%%' and
    user.is_invalid = 0 and
    user.is_blocked = 0
group by user.id, user.phone_number, user.updated_at, user.name, user.created_at
order by user.created_at desc 
limit 10 offset 0

额外说明 #

• 原查询中active_gift_card的统计逻辑存在问题：if(offer_item.is_complete=0,count(offer_item.id),0)会在is_complete=0时统计所有非null的offer_item.id，如果想统计未完成的礼品卡数量，应该用sum(if(offer_item.is_complete=0,1,0))，每条符合条件的记录计1，求和就是总数。
• MySQL严格模式下，GROUP BY子句需要包含所有非聚合的SELECT字段，所以修正后的查询补充了user.updated_at、user.name等字段。

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