如何提取数字左侧首位1并判定用户问题的SQL查询需求

核心思路 #

要实现需求，需将数字字段转换为字符串后分三步处理：

• 统计开头连续1的数量（每个对应不同问题类型）
• 截取去掉开头所有1后的剩余数字部分
• 计算剩余数字的位数，结合开头1的数量匹配对应问题信息

具体SQL实现 #

以下针对主流数据库给出实现示例：

MySQL 版本

SELECT
    username,
    i,
    -- 统计开头连续1的个数
    LEAST(CHAR_LENGTH(i), LOCATE(SUBSTRING(i, 1, 1), REPEAT('1', CHAR_LENGTH(i))) - 1) AS leading_ones_count,
    -- 截取剩余数字部分
    TRIM(LEADING '1' FROM CAST(i AS CHAR)) AS remaining_num,
    -- 计算剩余数字的位数（处理剩余部分为空的情况，比如i=111）
    CHAR_LENGTH(TRIM(LEADING '1' FROM CAST(i AS CHAR))) AS remaining_digits,
    -- 按规则映射错误类型（示例规则，可自行调整）
    CASE
        WHEN leading_ones_count = 1 THEN '类型A错误'
        WHEN leading_ones_count = 2 THEN '类型B错误'
        ELSE '未知错误类型'
    END AS error_type,
    -- 按剩余位数映射具体场景（示例规则，可自行调整）
    CASE
        WHEN remaining_digits = 2 THEN '场景X问题'
        WHEN remaining_digits = 3 THEN '场景Y问题'
        WHEN remaining_digits = 4 THEN '场景Z问题'
        ELSE '未知场景'
    END AS error_scenario
FROM users
WHERE i > 0; -- 过滤无错误的记录，可按需调整

SQL Server 版本

SELECT
    username,
    i,
    -- 统计开头连续1的个数
    PATINDEX('%[^1]%', CAST(i AS VARCHAR)) - 1 AS leading_ones_count,
    -- 截取剩余数字部分
    STUFF(CAST(i AS VARCHAR), 1, PATINDEX('%[^1]%', CAST(i AS VARCHAR)) - 1, '') AS remaining_num,
    -- 计算剩余数字的位数
    LEN(STUFF(CAST(i AS VARCHAR), 1, PATINDEX('%[^1]%', CAST(i AS VARCHAR)) - 1, '')) AS remaining_digits,
    -- 映射错误类型
    CASE
        WHEN leading_ones_count = 1 THEN '类型A错误'
        WHEN leading_ones_count = 2 THEN '类型B错误'
        ELSE '未知错误类型'
    END AS error_type,
    -- 映射具体场景
    CASE
        WHEN remaining_digits = 2 THEN '场景X问题'
        WHEN remaining_digits = 3 THEN '场景Y问题'
        WHEN remaining_digits = 4 THEN '场景Z问题'
        ELSE '未知场景'
    END AS error_scenario
FROM users
WHERE i > 0;

PostgreSQL 版本

SELECT
    username,
    i,
    -- 统计开头连续1的个数
    COALESCE(LENGTH(SUBSTRING(CAST(i AS TEXT) FROM '^1+')), 0) AS leading_ones_count,
    -- 截取剩余数字部分
    SUBSTRING(CAST(i AS TEXT) FROM '[^1].*') AS remaining_num,
    -- 计算剩余数字的位数
    COALESCE(LENGTH(SUBSTRING(CAST(i AS TEXT) FROM '[^1].*')), 0) AS remaining_digits,
    -- 映射错误类型
    CASE
        WHEN leading_ones_count = 1 THEN '类型A错误'
        WHEN leading_ones_count = 2 THEN '类型B错误'
        ELSE '未知错误类型'
    END AS error_type,
    -- 映射具体场景
    CASE
        WHEN remaining_digits = 2 THEN '场景X问题'
        WHEN remaining_digits = 3 THEN '场景Y问题'
        WHEN remaining_digits = 4 THEN '场景Z问题'
        ELSE '未知场景'
    END AS error_scenario
FROM users
WHERE i > 0;

关键说明 #

• 若字段i全为1（比如111），剩余数字部分会为空，此时remaining_digits为0，可根据业务需求补充对应处理逻辑
• 示例中的错误类型和场景映射规则仅作参考，需根据实际业务中每个前置1和剩余位数的含义调整CASE语句
• 确保字段i无负数，若存在负数需先处理符号部分

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