MySQL关联listings与logs表映射状态键值并分组统计数量的实现

最终实现SQL #

WITH 
-- 预定义所有状态映射，保证所有状态即使无数据也会输出
status_map AS (
    SELECT 'D' AS stat_key, 'Draft' AS stat_name UNION ALL
    SELECT 'A' AS stat_key, 'Action' AS stat_name UNION ALL
    SELECT 'Y' AS stat_key, 'Publish' AS stat_name UNION ALL
    SELECT 'S' AS stat_key, 'Sold' AS stat_name UNION ALL
    SELECT 'N' AS stat_key, 'Let' AS stat_name
),
-- 完全保留原有获取指定日期状态的业务逻辑
X AS (
    SELECT l.*,
           (SELECT status_from FROM logs WHERE logs.refno = l.refno AND logs.logtime >= '2021-10-01' ORDER BY logs.logtime LIMIT 1) logstat
      FROM listings l
      WHERE l.added_date < '2021-10-01'
),
-- 基于原有逻辑统计各状态实际数量
stat_count AS (
    SELECT IFNULL(logstat, status) AS final_stat, COUNT(*) AS cnt
      FROM X
      GROUP BY final_stat
)
-- 按要求拼接输出格式，无数据的状态数量默认填充0
SELECT CONCAT(m.stat_name, '(', m.stat_key, ')') AS `status`,
       IFNULL(c.cnt, 0) AS `Count`
  FROM status_map m
  LEFT JOIN stat_count c ON m.stat_key = c.final_stat
  ORDER BY m.stat_key;

整合逻辑说明 #

• 完全保留你原有获取指定日期商品状态的逻辑，没有修改任何业务判断规则
• 新增status_map公共表表达式预存所有状态的键值和对应名称，避免分组统计时遗漏数量为0的状态，替代重复写CASE WHEN的写法，后续状态调整也更易维护
• 先基于原有逻辑统计出各状态的实际数量，再和状态映射表左关联，没有匹配到的状态自动填充0，同时按照你要求的格式拼接status列的展示内容

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