SQL筛选以非零数字开头后接零的整数金额数据

你当前的SQL语句没考虑AMOUNT_LCY字段里的特殊格式（带括号的负数、千分位逗号、小数），直接用字符串匹配会漏掉大量符合条件的记录，得先把金额转换成标准数值再做判断。

核心处理逻辑 #

1. 清理金额格式：
   • 将带括号的负数（比如(1000)）转换为负号开头的格式（-1000）
   • 移除千分位的逗号（比如1,000转为1000）
   • 过滤掉小数部分不为0的记录（比如1000.50不符合要求，1000.00符合）
2. 数值匹配条件：
   • 要么是绝对值为1-9的单个整数
   • 要么是绝对值以1-9开头、后续全为0的整数（比如1000、4000等）

最终SQL语句 #

SELECT 
    [INPUTTER],
    [OUR_REFERENCE],
    [TRANS_REFERENCE],
    [COMMON_REF],
    [RECID],
    [ACCOUNT_NUMBER],
    [TRANSACTION_CODE],
    [NARRATIVE],
    [AMOUNT_LCY],
    [AMOUNT_FCY],
    [VALUE_DATE],
    [BOOKING_DATE],
    [CURRENCY],
    [EXCHANGE_RATE],
    [PRODUCT_CATEGORY],
    [PL_CATEGORY],
    [REVERSAL_MARKER]
FROM [Steward].[dbo].[vwSTMT_01] v
WHERE 
    -- 确保金额能成功转换为数值，避免报错
    TRY_CAST(
        REPLACE(REPLACE([AMOUNT_LCY], '(', '-'), ')', '') AS DECIMAL(18,2)
    ) IS NOT NULL
    AND (
        -- 条件1：绝对值是1-9的单个整数
        ABS(TRY_CAST(
            REPLACE(REPLACE([AMOUNT_LCY], '(', '-'), ')', '') AS DECIMAL(18,2)
        )) BETWEEN 1 AND 9
        AND TRY_CAST(
            REPLACE(REPLACE([AMOUNT_LCY], '(', '-'), ')', '') AS DECIMAL(18,2)
        ) = FLOOR(TRY_CAST(
            REPLACE(REPLACE([AMOUNT_LCY], '(', '-'), ')', '') AS DECIMAL(18,2)
        ))
        OR
        -- 条件2：绝对值是1-9开头、后续全为0的整数
        ABS(TRY_CAST(
            REPLACE(REPLACE([AMOUNT_LCY], '(', '-'), ')', '') AS DECIMAL(18,2)
        )) > 9
        AND TRY_CAST(
            REPLACE(REPLACE([AMOUNT_LCY], '(', '-'), ')', '') AS DECIMAL(18,2)
        ) = FLOOR(TRY_CAST(
            REPLACE(REPLACE([AMOUNT_LCY], '(', '-'), ')', '') AS DECIMAL(18,2)
        ))
        AND CAST(
            ABS(TRY_CAST(
                REPLACE(REPLACE([AMOUNT_LCY], '(', '-'), ')', '') AS DECIMAL(18,2)
            )) / POWER(10, LEN(CAST(ABS(TRY_CAST(
                REPLACE(REPLACE([AMOUNT_LCY], '(', '-'), ')', '') AS DECIMAL(18,2)
            )) AS VARCHAR)) - 1) AS INT
        ) BETWEEN 1 AND 9
        AND ABS(TRY_CAST(
            REPLACE(REPLACE([AMOUNT_LCY], '(', '-'), ')', '') AS DECIMAL(18,2)
        )) % POWER(10, LEN(CAST(ABS(TRY_CAST(
            REPLACE(REPLACE([AMOUNT_LCY], '(', '-'), ')', '') AS DECIMAL(18,2)
        )) AS VARCHAR)) - 1) = 0
    )

关键说明 #

• TRY_CAST用于避免格式错误的金额导致查询报错，只处理能正常转换为数值的记录
• 两次REPLACE处理带括号的负数格式
• FLOOR函数验证金额是否为整数（小数部分全为0）
• 最后两个条件组合验证：金额的第一位是1-9，且剩余位数全为0

内容的提问来源于stack exchange，提问作者Jimrosy P Madzokere