MySQL分组聚合查询如何按指定关联字段值自定义排序

表结构说明 #

业务共包含3张表：

• project（项目表）：存储项目基础数据，包含project_id、name两个字段，现有4条测试数据：
  • project_id=10000，对应名称Project 1
  • project_id=20000，对应名称Project 2
  • project_id=30000，对应名称Project 3
  • project_id=40000，对应名称Project 4
• revenues（营收表）：存储各项目对应不同公司的营收数据，包含project_id、revenue、fk_setting_id三个字段，现有测试记录：
  • 项目10000对应MARVEL营收2000、对应UNIVER营收3300
  • 项目20000对应MARVEL营收7000
  • 项目30000对应MARVEL营收1000、对应UNIVER营收15000
• company（公司表）：存储合作公司信息，包含setting_id、name两个字段，现有2条记录：
  • setting_id=10，名称MARVEL
  • setting_id=20，名称UNIVER

需求规则 #

需要支持传入两个动态参数实现查询：

• sort_key：指定作为排序依据的公司名称，例如传入"MARVEL"
• order_by：排序方向，取值仅为DESC（降序）/ASC（升序）
  返回要求：

1. 每个项目关联的所有公司营收数据需要聚合为JSON数组格式返回
2. 按照指定公司对应的项目营收值排序，无对应营收数据的项目统一排在末尾、聚合字段为空
   示例：按MARVEL营收降序排序时，结果顺序应为project_id=20000、10000、30000、40000

现有问题 #

已通过左连接+分组聚合子查询，结合JSON_OBJECT、GROUP_CONCAT、CONCAT函数实现了关联营收数据的JSON数组拼接，但无法实现动态排序逻辑，且原有SQL存在关联条件写错的问题，原代码如下：

SELECT p.project_id, p.name, stid.settings
FROM project p 
LEFT JOIN (SELECT sid.project_id, 
CONCAT('[', GROUP_CONCAT(
 JSON_OBJECT(
 'name', sas.name
,'revenue', sid.revenue
) SEPARATOR ',')
,']') AS settings
FROM revenues sid
-- 此处关联条件写反，会导致关联不到公司数据
JOIN company sas ON sas.fk_setting_id = sid.setting_id
GROUP BY sid.project_id) stid ON stid.project_id = p.project_id
LIMIT 0,20

修正后实现方案 #

实现逻辑：

1. 修复原SQL中营收表和公司表的关联条件错误
2. 额外左关联营收表、公司表，匹配传入的sort_key对应的公司营收值作为排序依据
3. 用CASE WHEN处理排序逻辑，保证无对应营收的项目无论升序降序都排在末尾
4. 对无营收数据的项目，聚合字段默认返回空数组[]
   最终可直接使用的SQL如下：

SELECT 
  p.project_id, 
  p.name, 
  IFNULL(stid.settings, '[]') AS settings
FROM project p 
LEFT JOIN (
  SELECT 
    sid.project_id, 
    CONCAT(
      '[', 
      GROUP_CONCAT(JSON_OBJECT('name', sas.name, 'revenue', sid.revenue) SEPARATOR ','),
      ']'
    ) AS settings
  FROM revenues sid
  -- 修正关联条件：company表主键是setting_id，关联revenues表的外键fk_setting_id
  INNER JOIN company sas ON sas.setting_id = sid.fk_setting_id
  GROUP BY sid.project_id
) stid ON stid.project_id = p.project_id
-- 关联拿到指定排序公司的对应营收
LEFT JOIN revenues sort_r 
  ON sort_r.project_id = p.project_id
LEFT JOIN company sort_c 
  ON sort_c.setting_id = sort_r.fk_setting_id
  AND sort_c.name = ? -- 预编译传入sort_key参数，例如'MARVEL'
ORDER BY
  -- 降序排序逻辑，null值自动排在末尾
  CASE WHEN ? = 'DESC' THEN sort_r.revenue END DESC,
  -- 升序排序逻辑，null值自动排在末尾
  CASE WHEN ? = 'ASC' THEN sort_r.revenue END ASC
LIMIT 0,20

注意事项 #

• order_by参数无法通过SQL预编译传参，必须在业务代码层做严格白名单校验，仅允许传入ASC/DESC两个值，避免SQL注入风险
• 如果使用MySQL 8.0及以上版本，可将手动拼接JSON的逻辑替换为JSON_ARRAYAGG(JSON_OBJECT('name', sas.name, 'revenue', sid.revenue)) AS settings，比GROUP_CONCAT拼接更可靠，不会因为字段值包含逗号导致JSON格式错误
• 上述SQL按MARVEL降序测试时，返回顺序完全符合预期：20000（营收7000）、10000（营收2000）、30000（营收1000）、40000（无MARVEL营收）

内容的提问来源于stack exchange，提问作者Muhammad Zahid Iqbal