MySQL存储过程实现状态级联累计计数功能需求

原查询功能 #

当前存储过程中的查询可返回指定日期范围内（p_StartDate至p_EndDate）所有非0/1状态的每日计数及累计计数，原代码如下：

DROP TEMPORARY TABLE IF EXISTS date_range;
CREATE TEMPORARY TABLE date_range AS
SELECT DATE(p_StartDate) + INTERVAL (n-1) DAY AS date
FROM (
  SELECT ROW_NUMBER() OVER () AS n
  FROM detail 
  LIMIT 31 
) AS dates;


DROP TEMPORARY TABLE IF EXISTS date_status_combinations;
-- 生成日期与所有状态的全组合
CREATE TEMPORARY TABLE date_status_combinations AS
SELECT ps.Id,ps.Status, dr.date
FROM date_range dr
CROSS JOIN productionstatus ps ;


SELECT
  dsc.Status,
  DATE(dsc.date) AS StatusDateTime,
  COALESCE(d.count, 0) AS count,
  SUM(COALESCE(d.count, 0)) OVER (PARTITION BY dsc.Status ORDER BY DATE(dsc.date)) AS cumulative_count
FROM date_status_combinations dsc  
LEFT JOIN (
  SELECT
    ProductionStatus,
    DATE(StatusDateTime) AS date,
    COUNT(*) AS count
  FROM detailunique
  WHERE DATE(StatusDateTime) BETWEEN DATE(p_StartDate) AND DATE(p_EndDate)
  GROUP BY ProductionStatus, DATE(StatusDateTime)
) d ON dsc.Status = d.ProductionStatus AND DATE(dsc.date) = DATE(d.date) 
WHERE  dsc.Id not in (0,1)
ORDER BY dsc.Status, DATE(dsc.date);

需求新增 #

需要添加状态级联计数逻辑：例如状态7的记录，需同时计入状态2、3、4、5、6的每日计数及累计统计中。

修改后的实现代码 #

-- 1. 创建日期范围临时表（保留原有逻辑）
DROP TEMPORARY TABLE IF EXISTS date_range;
CREATE TEMPORARY TABLE date_range AS
SELECT DATE(p_StartDate) + INTERVAL (n-1) DAY AS date
FROM (
  SELECT ROW_NUMBER() OVER () AS n
  FROM detail 
  LIMIT 31 
) AS dates;

-- 2. 新增状态级联映射表：定义每个原始状态需要计入的目标状态
DROP TEMPORARY TABLE IF EXISTS status_cascade_map;
CREATE TEMPORARY TABLE status_cascade_map (
  original_status_id INT,
  target_status_id INT
);
-- 所有非0/1状态默认计入自身
INSERT INTO status_cascade_map
SELECT Id, Id FROM productionstatus WHERE Id NOT IN (0,1);
-- 添加状态7的级联规则：计入状态2-6
INSERT INTO status_cascade_map
VALUES (7,2), (7,3), (7,4), (7,5), (7,6);

-- 3. 创建日期-状态组合临时表（保留原有逻辑）
DROP TEMPORARY TABLE IF EXISTS date_status_combinations;
CREATE TEMPORARY TABLE date_status_combinations AS
SELECT ps.Id, ps.Status, dr.date
FROM date_range dr
CROSS JOIN productionstatus ps ;

-- 4. 主查询：基于级联映射统计每日计数和累计值
SELECT
  dsc.Status,
  DATE(dsc.date) AS StatusDateTime,
  COALESCE(d.count, 0) AS count,
  SUM(COALESCE(d.count, 0)) OVER (PARTITION BY dsc.Status ORDER BY DATE(dsc.date)) AS cumulative_count
FROM date_status_combinations dsc  
LEFT JOIN (
  -- 核心修改：通过级联映射展开所有需要计入的状态
  SELECT
    scm.target_status_id,
    ps.Status AS target_status,
    DATE(d.StatusDateTime) AS date,
    COUNT(*) AS count
  FROM detailunique d
  -- 关联级联映射表，将原始状态转为所有目标状态
  JOIN status_cascade_map scm ON d.ProductionStatus = scm.original_status_id
  JOIN productionstatus ps ON scm.target_status_id = ps.Id
  WHERE DATE(d.StatusDateTime) BETWEEN DATE(p_StartDate) AND DATE(p_EndDate)
  -- 按目标状态和日期分组统计
  GROUP BY scm.target_status_id, ps.Status, DATE(d.StatusDateTime)
) d ON dsc.Id = d.target_status_id AND DATE(dsc.date) = DATE(d.date) 
WHERE dsc.Id NOT IN (0,1)
ORDER BY dsc.Status, DATE(dsc.date);

关键改动说明 #

• 新增status_cascade_map临时表：集中管理状态级联规则，后续修改规则只需调整INSERT语句，无需改动核心统计逻辑
• 统计子查询通过关联映射表，将每条原始记录的状态展开为所有需要计入的目标状态，确保级联计数正确
• 保留原有日期范围、日期-状态组合的逻辑，保证输出格式与原查询一致，仅调整计数来源

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