MySQL JOIN查询本地XAMPP正常线上报1064语法错误排查求助

你遇到的1064语法错误核心是GROUP_CONCAT函数中使用的LIMIT子句属于MySQL 8.0.29版本才新增的特性：

• 本地XAMPP环境的MySQL版本≥8.0.29，支持该语法因此运行正常
• 线上服务器的MySQL版本低于8.0.29，无法识别GROUP_CONCAT内的LIMIT语法，因此触发报错

无需依赖高版本MySQL特性，通过子查询先筛选每个小说的最新3条章节数据，再做聚合即可，同时还修复了原SQL中GROUP BY字段不完整不符合SQL_MODE规范的问题：

SELECT 
  n.NovelID, 
  n.NovelName,
  n.NovelImage,
  GROUP_CONCAT(c.chapternumber ORDER BY c.chapterID DESC) AS chapname,
  GROUP_CONCAT(c.chaptername ORDER BY c.chapterID DESC) AS chpnum
FROM novel n
INNER JOIN (
  SELECT 
    chapters.*,
    ROW_NUMBER() OVER (PARTITION BY NovelID ORDER BY chapterID DESC) AS rn
  FROM chapters 
  WHERE DATE(chapterDate) >= CURDATE()
) c ON n.NovelID = c.NovelID AND c.rn <=3
GROUP BY n.NovelID, n.NovelName, n.NovelImage

如果你的线上MySQL版本低于8.0，不支持窗口函数，可使用以下兼容MySQL 5.x的写法：

SELECT 
  n.NovelID, 
  n.NovelName,
  n.NovelImage,
  GROUP_CONCAT(c.chapternumber ORDER BY c.chapterID DESC) AS chapname,
  GROUP_CONCAT(c.chaptername ORDER BY c.chapterID DESC) AS chpnum
FROM novel n
INNER JOIN chapters c ON n.NovelID = c.NovelID
WHERE DATE(c.chapterDate) >= CURDATE()
AND (
  SELECT COUNT(*) 
  FROM chapters c2 
  WHERE c2.NovelID = c.NovelID AND c2.chapterID >= c.chapterID AND DATE(c2.chapterDate) >= CURDATE()
) <=3
GROUP BY n.NovelID, n.NovelName, n.NovelImage

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