含GROUP_CONCAT与窗口函数的MySQL查询排序不符合预期求助

原因 #

你的查询中使用了GROUP BY cl.id，同时在ORDER BY中直接引用非聚合列cl.name。虽然cl.name和cl.id存在函数依赖（cl.id是主键，每个id对应唯一的name），但当查询中同时包含GROUP_CONCAT和窗口函数COUNT(*) OVER()时，MySQL的查询优化器可能没有正确识别这种依赖关系，导致排序时使用的cl.name并非分组后对应行的确定值，而是关联过程中的临时值，最终造成排序不符合预期。

解决方案 #

以下几种方案可以保留原有查询结构并解决排序问题：

方案1：在ORDER BY中使用聚合函数包裹cl.name #

因为每个cl.id对应的cl.name是唯一的，使用MAX()或MIN()聚合函数可以确保获取到分组后正确的name值，从而正常排序：

SELECT 
  cl.id,
  cl.name,
  GROUP_CONCAT(s.s_name ORDER BY s.s_name) AS s_names,
  COUNT(*) OVER() as total_rows
FROM cl
LEFT JOIN cl_s ON cl.id = cl_s.cl_id
LEFT JOIN s ON cl_s.s_id = s.id
GROUP BY cl.id
ORDER BY MAX(cl.name) DESC

方案2：在SELECT中聚合cl.name并使用别名排序 #

先在SELECT列表中用聚合函数确定cl.name的值，再通过别名排序，效果和方案1一致：

SELECT 
  cl.id,
  MAX(cl.name) AS cl_name,
  GROUP_CONCAT(s.s_name ORDER BY s.s_name) AS s_names,
  COUNT(*) OVER() as total_rows
FROM cl
LEFT JOIN cl_s ON cl.id = cl_s.cl_id
LEFT JOIN s ON cl_s.s_id = s.id
GROUP BY cl.id
ORDER BY cl_name DESC

方案3：确保ONLY_FULL_GROUP_BY模式生效（可选） #

如果你的MySQL版本支持，开启ONLY_FULL_GROUP_BY SQL模式后，优化器会更严格地处理函数依赖关系，可能直接使用ORDER BY cl.name DESC就能正常排序。可以通过以下语句临时开启：

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';

之后执行原查询即可。不过这种方式依赖MySQL对函数依赖的识别，不如前两种方案稳定。

内容的提问来源于stack exchange，提问作者מוישי דויטש