如何在MySQL中生成指定JSON格式的total_sales列并解决聚合错误

目标查询结果 #

我需要通过SELECT查询得到上述员工统计结果，最后一列total_sales需以JSON格式展示当日每位员工创造的总营收数据。

尝试过两种写法都有问题：

• 用JSON_ARRAYAGG(JSON_OBJECT(emp.name , ad.price)) AS total_sales，只能返回当日的首个价格，无法聚合员工的总销售额；
• 尝试在JSON_OBJECT里加SUM(ad.price)，写成JSON_ARRAYAGG(JSON_OBJECT(emp.name , SUM(ad.price))) AS total_sales，直接报错。

附上当前使用的SQL代码：

SET @formDate=DATE_SUB(now(),INTERVAL 30 DAY);

SELECT 
      DATE(appoint.booking_date) AS 'date',
      DATE_FORMAT(appoint.booking_date,'%M') AS 'month',
      (SELECT COUNT(DISTINCT emp.id) FROM employee emp WHERE emp.entity_id='126' AND emp.status=1) AS 'total_employee',
      IFNULL(SUM(ad.price),0) AS total_revenue,
      JSON_ARRAYAGG(JSON_OBJECT(emp.name , ad.price)) AS total_sales
      FROM employee emp 
      
      LEFT JOIN entity ent ON emp.entity_id=ent.id
      LEFT JOIN appointment_status appstat ON emp.entity_id=appstat.entity_id
      LEFT JOIN appointments appoint ON appstat.appointment_id=appoint.id
      LEFT JOIN appointment_details ad ON appoint.id=ad.appointment_id

      WHERE 
          emp.status=1 AND
          emp.entity_id='126' AND
          appstat.current_status='4' AND
          appstat.assign_to = emp.account_id AND
          DATE_FORMAT(appoint.booking_date,'%Y-%m-%d')
                    BETWEEN DATE_FORMAT(@formDate,'%Y-%m-%d') AND 
                            DATE_FORMAT(now(),'%Y-%m-%d') 
      
      GROUP BY
             appoint.booking_date DESC

解决方法 #

问题核心是聚合逻辑分层错误：不能直接在JSON_OBJECT中嵌套聚合函数，需要先按日期+员工聚合出每个员工的日销售额，再在外层生成JSON数组。

方案1：子查询先聚合员工日销售额（符合目标格式）

先通过子查询计算每个员工每天的总销售额，再在外层按日期聚合并生成目标JSON数组：

SET @formDate = DATE_SUB(NOW(), INTERVAL 30 DAY);

SELECT
    daily_stats.date,
    daily_stats.month,
    (SELECT COUNT(DISTINCT emp.id) FROM employee emp WHERE emp.entity_id='126' AND emp.status=1) AS total_employee,
    SUM(daily_stats.emp_revenue) AS total_revenue,
    JSON_ARRAYAGG(JSON_OBJECT(daily_stats.emp_name, daily_stats.emp_revenue)) AS total_sales
FROM (
    SELECT
        DATE(appoint.booking_date) AS date,
        DATE_FORMAT(appoint.booking_date, '%M') AS month,
        emp.name AS emp_name,
        IFNULL(SUM(ad.price), 0) AS emp_revenue
    FROM employee emp
    LEFT JOIN appointment_status appstat ON emp.entity_id = appstat.entity_id
    LEFT JOIN appointments appoint ON appstat.appointment_id = appoint.id
    LEFT JOIN appointment_details ad ON appoint.id = ad.appointment_id
    WHERE
        emp.status = 1
        AND emp.entity_id = '126'
        AND appstat.current_status = '4'
        AND appstat.assign_to = emp.account_id
        AND DATE(appoint.booking_date) BETWEEN @formDate AND CURDATE()
    GROUP BY DATE(appoint.booking_date), emp.id, emp.name
) AS daily_stats
GROUP BY daily_stats.date, daily_stats.month
ORDER BY daily_stats.date DESC;

方案2：用JSON_OBJECTAGG简化格式（可选）

如果可以接受total_sales是单个JSON对象（比如{"Debasish":500,"Sanjana":120}），可以用JSON_OBJECTAGG直接生成更简洁的结构：

SET @formDate = DATE_SUB(NOW(), INTERVAL 30 DAY);

SELECT
    DATE(appoint.booking_date) AS date,
    DATE_FORMAT(appoint.booking_date, '%M') AS month,
    (SELECT COUNT(DISTINCT emp.id) FROM employee emp WHERE emp.entity_id='126' AND emp.status=1) AS total_employee,
    IFNULL(SUM(ad.price), 0) AS total_revenue,
    JSON_OBJECTAGG(emp.name, IFNULL(SUM(ad.price), 0)) AS total_sales
FROM employee emp
LEFT JOIN appointment_status appstat ON emp.entity_id = appstat.entity_id
LEFT JOIN appointments appoint ON appstat.appointment_id = appoint.id
LEFT JOIN appointment_details ad ON appoint.id = ad.appointment_id
WHERE
    emp.status = 1
    AND emp.entity_id = '126'
    AND appstat.current_status = '4'
    AND appstat.assign_to = emp.account_id
    AND DATE(appoint.booking_date) BETWEEN @formDate AND CURDATE()
GROUP BY DATE(appoint.booking_date), DATE_FORMAT(appoint.booking_date, '%M')
ORDER BY DATE(appoint.booking_date) DESC;

关键调整说明 #

1. 原代码GROUP BY appoint.booking_date DESC错误：booking_date是datetime类型，直接分组会按精确时间而非日期分组，改为按DATE(appoint.booking_date)分组；
2. 日期比较简化：用DATE()函数转换后直接和日期变量比较，比DATE_FORMAT更高效；
3. 聚合分层：子查询先按日期+员工聚合，确保每个员工每天只有一条销售总额记录，外层再生成JSON数组，避免重复或错误数据。

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