MariaDB/MySQL CTE原始SQL语法报错：near 'group as (' 语法错误

错误根源 #

group是MariaDB的保留关键字，不能直接用作CTE（公共表表达式）的别名，这就是触发语法错误的直接原因。

修正方案及优化点 #

1. 替换关键字别名：将CTE的group别名改为非关键字名称（比如target_group），同时同步修改查询末尾的from group语句
2. 清理冗余参数：原SQL中多个?指向同一个group ID，可通过CTE复用减少重复占位符
3. 移除多余逗号：原select语句末尾templates_count,的逗号属于语法错误，必须删除
4. 修正子查询逻辑：(select * from groups)会返回所有分组数据，作为列返回会导致结果混乱，建议根据实际需求调整（比如直接使用CTE中的字段）

修正后的完整SQL #

with 
    target_group as (
     select * from groups where id = ?
    ),
    attributes as (
     select JSON_ARRAYAGG(
     JSON_OBJECT('id', a.id,'name', a.name )) as attributes_list
     from attributes a
     join target_group g on g.id = a.group_id
     join attribute_group ag on ag.group_id = g.id
     and ag.attribute_id = a.id
    ),
    templates as (
     select JSON_ARRAYAGG(
     JSON_OBJECT('id', t.id,'name', t.name)) as templates_list
     from templates t
     join target_group g on g.id = t.group_id
     join group_template gt on gt.group_id = g.id
     and gt.template_id = t.id
    )

select 
    tg.*, 
    (select cast(count(*) as char) from attribute_group where group_id = tg.id) as groups_count,
    tg.id as group_id, tg.name as group_name,
    (select cast(count(*) as char) from group_template where group_id = tg.id) as templates_count,
    (select attributes_list from attributes) as attributes,
    (select templates_list from templates) as templates
from target_group tg

额外说明 #

• 日常开发中尽量避免用数据库保留关键字作为表、别名、字段名，从根源规避这类语法问题
• 复用CTE中的数据可以减少参数传递的重复，也让查询逻辑更清晰

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