SQL实现：基于日期条件替换历史编码右侧首个非零值为序列值

我正在编写包含嵌套查询的SQL语句，需要基于paidAt和deadlineDate的日期条件生成「历史编码」，并对该编码做如下修改：

• 找到编码中从右侧数第一个非零值，按规则替换：
  • 若为1，替换为10
  • 若为2，替换为210
  • 若为3，替换为3210
  • …
  • 若为6，替换为6543210
  • 若为W，替换为W6543210
• 编码其余部分保持不变。

示例：历史编码0302230需转换为0302232100，即将右侧首个非零值3替换为3210。

当前使用的SQL代码 #

SELECT
GROUP_CONCAT(
CASE
WHEN i.paidAt IS NULL
AND DATE_ADD(i.deadlineDate, INTERVAL 10 DAY) < CURDATE()
AND i.dueDate = (
SELECT MIN(i2.dueDate)
FROM installment i2
WHERE i2.contractId = i.contractId
AND i2.paidAt IS NULL
AND DATE_ADD(i2.deadlineDate, INTERVAL 10 DAY) < CURDATE()
LIMIT 1
)
THEN
CASE
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) <= 0 THEN '0'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 1 AND 29 THEN '1'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 30 AND 59 THEN '2'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 60 AND 89 THEN '3'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 90 AND 119 THEN '4'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 120 AND 149 THEN '5'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 150 AND 179 THEN '6'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) >= 180 THEN 'W'
END
ELSE
CASE
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) <= 0 THEN '0'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 1 AND 29 THEN '1'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 30 AND 59 THEN '2'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 60 AND 89 THEN '3'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 90 AND 119 THEN '4'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 120 AND 149 THEN '5'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 150 AND 179 THEN '6'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) >= 180 THEN 'W'
END
END ORDER BY i.dueDate DESC SEPARATOR ''
)
FROM installment i
WHERE i.contractId = c.id
) AS 'Payment History Status Code',

解决方案 #

通过字符串反转、定位替换的方式实现需求，修改后的SQL如下：

SELECT
CONCAT(
  -- 截取非零字符左侧部分
  LEFT(original_code, LENGTH(original_code) - POSITION(non_zero_char IN REVERSE(original_code))),
  -- 根据规则替换非零字符
  CASE non_zero_char
    WHEN '1' THEN '10'
    WHEN '2' THEN '210'
    WHEN '3' THEN '3210'
    WHEN '4' THEN '43210'
    WHEN '5' THEN '543210'
    WHEN '6' THEN '6543210'
    WHEN 'W' THEN 'W6543210'
    ELSE non_zero_char
  END,
  -- 截取非零字符右侧部分
  RIGHT(original_code, POSITION(non_zero_char IN REVERSE(original_code)) - 1)
) AS 'Modified Payment History Status Code'
FROM (
  -- 子查询：生成原始编码并定位右侧首个非零字符
  SELECT
    original_code,
    SUBSTRING(
      REVERSE(original_code),
      POSITION(REGEXP_SUBSTR(REVERSE(original_code), '[^0]') IN REVERSE(original_code)),
      1
    ) AS non_zero_char
  FROM (
    -- 原逻辑生成原始历史编码
    SELECT
      GROUP_CONCAT(
        CASE
          WHEN i.paidAt IS NULL
               AND DATE_ADD(i.deadlineDate, INTERVAL 10 DAY) < CURDATE()
               AND i.dueDate = (
                 SELECT MIN(i2.dueDate)
                 FROM installment i2
                 WHERE i2.contractId = i.contractId
                       AND i2.paidAt IS NULL
                       AND DATE_ADD(i2.deadlineDate, INTERVAL 10 DAY) < CURDATE()
                 LIMIT 1
               )
          THEN
            CASE
              WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) <= 0 THEN '0'
              WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 1 AND 29 THEN '1'
              WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 30 AND 59 THEN '2'
              WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 60 AND 89 THEN '3'
              WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 90 AND 119 THEN '4'
              WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 120 AND 149 THEN '5'
              WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 150 AND 179 THEN '6'
              WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) >= 180 THEN 'W'
            END
          ELSE
            CASE
              WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) <= 0 THEN '0'
              WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 1 AND 29 THEN '1'
              WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 30 AND 59 THEN '2'
              WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 60 AND 89 THEN '3'
              WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 90 AND 119 THEN '4'
              WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 120 AND 149 THEN '5'
              WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 150 AND 179 THEN '6'
              WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) >= 180 THEN 'W'
            END
        END ORDER BY i.dueDate DESC SEPARATOR ''
      ) AS original_code
    FROM installment i
    WHERE i.contractId = c.id
  ) AS code_subquery
) AS final_subquery;

逻辑说明 #

1. 生成原始编码：保留原查询的GROUP_CONCAT逻辑，生成初始历史编码original_code。
2. 定位目标字符：
   • 反转原始编码，将“从右侧找非零值”转为“从左侧找非零值”。
   • 用正则匹配反转后第一个非零字符，定位其位置并截取该字符。
3. 拼接替换结果：拆分原始编码为目标字符的左右两部分，中间插入对应规则的替换值，拼接得到最终编码。

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