MySQL pivot table查询存在语法错误无法定位，请求协助排查

MySQL Pivot查询语法错误修复方案 #

核心错误原因 #

• 字段名和SQL关键字冲突：你的calendar表使用date作为字段名，和MySQL内置的DATE函数重名，未用反引号包裹时会被解析器误识别为函数调用，就是你遇到的SELECT GROUP_CONCAT附近报错的直接原因
• 空值未兜底：如果指定日期区间内calendar表没有数据，@sql会为NULL，后续拼接查询语句会整体失效
• 字段归属不清晰：未指定date字段所属表，属于不严谨的写法，部分SQL模式下会触发歧义报错

修正后完整代码 #

-- 先设置足够长的group_concat长度，避免拼接的语句被截断
SET SESSION group_concat_max_len = 1000000;
SET @sql = ''; -- 初始化为空字符串而非NULL，避免后续拼接为空

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN ca.date = ''',
      date_format(c.`date`, '%Y-%m-%d'), -- 用反引号包裹date字段，指定表别名c
      ''' THEN coalesce(p.status, ''P'') END) AS `',
      date_format(c.`date`, '%Y-%m-%d'), '`'
    )
  ) INTO @sql
FROM calendar c -- 给calendar表加别名，简化后续字段引用
WHERE c.`date` >= '2013-06-01'
  AND c.`date` <= '2013-06-05';

-- 仅当@sql不为空时拼接完整查询，避免语法错误
IF @sql IS NOT NULL AND @sql != '' THEN
  SET @sql 
    = CONCAT('SELECT ca.studentname,
                ca.rollno,
                ca.class, ', @sql, ' 
              FROM
              (
                SELECT c.date, a.studentname, a.rollno, a.class
                FROM calendar c
                CROSS JOIN tbl_admission a
              ) ca
              LEFT JOIN tbl_absentees p
                ON ca.rollno = p.rollno
                AND ca.date = p.date
              WHERE ca.date >= ''2013-06-01''
                AND ca.date <= ''2013-06-05''
              GROUP BY ca.studentname, ca.rollno, ca.class
              ORDER BY ca.rollno');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END IF;

额外注意事项 #

如果你是在命令行或者Navicat等可视化工具中执行上述代码，需要先修改语句分隔符，避免IF代码块里的分号被提前识别为语句结束：

DELIMITER //
-- 上述完整代码放在此处
//
DELIMITER ;

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