为何该SQL查询在MySQL正常运行却在MariaDB报错？能否兼容两者？

报错原因 #

MySQL和MariaDB对嵌套子查询中列别名的解析规则存在差异：

• MySQL允许深层嵌套的子查询引用外层SELECT语句中定义的列别名（比如这里的uID）；
• 但MariaDB的别名作用域不包含深层嵌套子查询，内层子查询无法识别外层SELECT定义的uID别名，因此抛出「Unknown column 'uID' in 'where clause'」错误。

兼容两个数据库的查询版本 #

核心思路是避免在嵌套子查询中引用外层SELECT的列别名，直接通过表别名引用外层表的原始列：

select up.user_id as uID, last_name, first_name, athlete, parent, coach, code,
   sum(up.points) as total,
   propoints as lifetime,
   (select count(*) / 7 from 
        (select count(user_id), Date(created_at) as date
          from user_propoints
          where user_id = up.user_id
          group by date) as ac1)
    as activity
from user_propoints up
left join users on users.id = up.user_id
left join countries on users.country = countries.id
group by up.user_id
order by total desc

修改说明 #

1. 给外层的user_propoints表起别名up；
2. 将内层子查询中的where user_id = uID改为where user_id = up.user_id，直接引用外层表的原始user_id列；
3. 外层查询中涉及user_propoints的列也通过别名up明确指定，避免歧义。

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