Sql数据库列表,搜索时,页面列表字段会显示异常


类似每次搜索后,之前的结果依然还在,列表是从通过sql语言查询的结果创建的数据表。
这个还是比较影响实用,每次都要重现刷新页面。

麻烦在这里给个最小化复现: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;


应该是显示哪里出问题了