MySQL 8.0.20子查询生成动态透视表语法错误求助

问题分析 #

报错提示的语法问题根源有两个：

• 动态生成的日期列名（如2022-11-01）包含特殊字符，未用反引号包裹，导致SQL解析失败
• GROUP BY NIK不符合MySQL sql_mode中的ONLY_FULL_GROUP_BY规则（默认开启），name列未被聚合或包含在GROUP BY子句中

修复后的SQL代码 #

set @m=11; -- 月份
set @y=2022; -- 年份
set @sql=NULL; -- 存储动态生成的透视列SQL

-- 生成动态透视列，给日期列名添加反引号
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when z.dt = ''',
      dt,
      ''' then tIn end) `',
      dt,
      '`'
    )
  ) INTO @sql 
FROM
  (
SELECT
 e.NIK, e.name, u.dt, IFNULL(m.tIn,IF(IFNULL(tm.title,gs.ShiftDesc) LIKE 'OFF%','OFF', CONCAT(gs.tIn,' -(d)'))) tIn
FROM
mstEmp e
CROSS JOIN (
  SELECT
      date(CONCAT( @y, '/', @m, '/', t.dt )) dt 
    FROM
        `--utitilityTable1` t 
    WHERE
    t.dt BETWEEN 1 AND DAY (LAST_DAY(CONCAT( @y, '/', @m, '/1' ))))u
LEFT JOIN 
transaksiChangeShift cs ON e.NIK = cs.NIK AND u.dt BETWEEN cs.dateStart AND cs.dateEnd
LEFT JOIN timeTitleMaster tm ON cs.idST = tm.idST
LEFT JOIN timeMaster m ON tm.idST = m.idST AND WEEKDAY(u.dt)=IF(FIND_IN_SET(WEEKDAY(u.dt),m.dayID)=0,NULL,WEEKDAY(u.dt))
LEFT JOIN mstWorkgroup w ON e.idWG = w.idWG
LEFT JOIN mstGroupShift gs ON w.idGroupShift = gs.idGroupShift AND WEEKDAY(u.dt)=gs.Idx)z;

SET @sql = CONCAT('SELECT NIK, name, ', @sql, ' 
                  FROM (
SELECT
 e.NIK, e.name, u.dt, IFNULL(m.tIn,IF(IFNULL(tm.title,gs.ShiftDesc) LIKE ''OFF%'',''OFF'', CONCAT(gs.tIn,'' -(d)''))) tIn
FROM
mstEmp e
CROSS JOIN  (
  SELECT
      date(CONCAT( @y, ''/'', @m, ''/'', t.dt )) dt 
    FROM
        `--utitilityTable1` t 
    WHERE
    t.dt BETWEEN 1 AND DAY (LAST_DAY(CONCAT( @y, ''/'', @m, ''/1'' ))))u
LEFT JOIN 
transaksiChangeShift cs ON e.NIK = cs.NIK AND u.dt BETWEEN cs.dateStart AND cs.dateEnd
LEFT JOIN timeTitleMaster tm ON cs.idST = tm.idST
LEFT JOIN timeMaster m ON tm.idST = m.idST AND WEEKDAY(u.dt)=IF(FIND_IN_SET(WEEKDAY(u.dt),m.dayID)=0,NULL,WEEKDAY(u.dt))
LEFT JOIN mstWorkgroup w ON e.idWG = w.idWG
LEFT JOIN mstGroupShift gs ON w.idGroupShift = gs.idGroupShift AND WEEKDAY(u.dt)=gs.Idx)z 
                   GROUP BY NIK, name -- 添加上name列，符合ONLY_FULL_GROUP_BY规则
                   ');

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

关键修复点说明 #

• 给动态列名添加反引号：在GROUP_CONCAT生成的片段中，把日期列名用`包裹（CONCAT(..., '', dt, '')），避免因列名包含-符号导致语法解析错误
• 修正GROUP BY子句：将GROUP BY NIK改为GROUP BY NIK, name，符合MySQL默认的ONLY_FULL_GROUP_BY模式要求，确保查询结果合法

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