麻烦在这里给个最小化复现：https://a_j89r6gwvkw4.v7.demo-cn.nocobase.com/admin/5ve30131jqw
线上数据太少，不太好复现，可以提供我使用的配置方式，可供参考。
WITH
– 步骤1：计算月度数据
monthly_data AS (
SELECT
wuliao_name,
pinpai,
xinghao,
danwei,
DATE_FORMAT(STR_TO_DATE(xiadanriqi, ‘%Y-%m-%d’), ‘%Y-%m’) AS yuefen,
YEAR(STR_TO_DATE(xiadanriqi, ‘%Y-%m-%d’)) AS nianfen,
SUM(shuliang) AS total_shuliang,
SUM(shuliang * danjia) AS total_jine,
ROUND(SUM(shuliang * danjia) / SUM(shuliang), 2) AS yue_avg
FROM gxianshangdingdanshuju
GROUP BY
wuliao_name, pinpai, xinghao,danwei,
YEAR(STR_TO_DATE(xiadanriqi, ‘%Y-%m-%d’)),
DATE_FORMAT(STR_TO_DATE(xiadanriqi, ‘%Y-%m-%d’), ‘%Y-%m’)
),
– 步骤2：计算季度数据（动态过滤未来季度）
quarter_data AS (
SELECT
wuliao_name,
pinpai,
xinghao,
danwei,
YEAR(real_date) AS nianfen,
QUARTER(real_date) AS jidu,
SUM(shuliang) AS total_shuliang,
SUM(shuliang * danjia) AS total_jine,
ROUND(SUM(shuliang * danjia) / SUM(shuliang), 2) AS jidu_avg
FROM (
SELECT
wuliao_name,
pinpai,
xinghao,
danwei,
STR_TO_DATE(xiadanriqi, ‘%Y-%m-%d’) AS real_date,
shuliang,
danjia,
– 动态计算季度结束日
CASE
WHEN QUARTER(CURDATE()) = 1 THEN CONCAT(YEAR(CURDATE()), ‘-03-31’)
WHEN QUARTER(CURDATE()) = 2 THEN CONCAT(YEAR(CURDATE()), ‘-06-30’)
WHEN QUARTER(CURDATE()) = 3 THEN CONCAT(YEAR(CURDATE()), ‘-09-30’)
ELSE CONCAT(YEAR(CURDATE()), ‘-12-31’)
END AS current_quarter_end
FROM gxianshangdingdanshuju
) t
WHERE real_date <= current_quarter_end
GROUP BY
wuliao_name, pinpai, xinghao,danwei,
YEAR(real_date),
QUARTER(real_date)
),
– 步骤3：计算年度数据
yearly_data AS (
SELECT
wuliao_name,
pinpai,
xinghao,
danwei,
YEAR(STR_TO_DATE(xiadanriqi, ‘%Y-%m-%d’)) AS nianfen,
SUM(shuliang) AS total_shuliang,
SUM(shuliang * danjia) AS total_jine,
ROUND(SUM(shuliang * danjia) / SUM(shuliang), 2) AS nian_avg
FROM gxianshangdingdanshuju
GROUP BY
wuliao_name, pinpai, xinghao,danwei,
YEAR(STR_TO_DATE(xiadanriqi, ‘%Y-%m-%d’))
)
– 步骤4：合并结果
SELECT
– CONCAT_WS(‘+’, q.wuliao_name, q.pinpai, q.xinghao) AS wuliaomingcheng,
q.wuliao_name AS wuliaomingcheng,
q.pinpai as pinpai,
q.xinghao as xinghao,
q.nianfen AS nianfen,
q.danwei as danwei,
– 合并季度数据（保证唯一性）
GROUP_CONCAT(
DISTINCT CONCAT(‘第’, q.jidu, '季度: ', q.jidu_avg)
ORDER BY q.jidu
SEPARATOR ’ | ’
) AS jidujunjia,
y.nian_avg AS `niandujunjia`,
-- 合并月度数据
GROUP_CONCAT(
DISTINCT CONCAT(m.yuefen, ': ', m.yue_avg)
ORDER BY m.yuefen
SEPARATOR ' | '
) AS `yuedumingxi`
FROM quarter_data q
JOIN yearly_data y
ON q.wuliao_name = y.wuliao_name
AND q.pinpai = y.pinpai
AND q.xinghao = y.xinghao
AND q.nianfen = y.nianfen
AND q.danwei = y.danwei
LEFT JOIN monthly_data m
ON q.wuliao_name = m.wuliao_name
AND q.pinpai = m.pinpai
AND q.xinghao = m.xinghao
AND q.nianfen = m.nianfen
AND q.danwei = m.danwei
GROUP BY q.wuliao_name, q.pinpai, q.xinghao, q.nianfen,q.danwei
ORDER BY nianfen desc ,wuliaomingcheng;
