SQL统计动态字符串中|前小于10的数值数量及总数

给定动态字符串（示例：'c:\-231.53 Gb-36.08 Gb-19.58|231.53 Gb-36.08 Gb-1.58|231.53 Gb-36.08 Gb-9.58|'），需提取每个|分隔段的最后一个数值，统计其中小于10的数量，最终输出小于10的数量/总数量格式的结果。

MySQL 8.0+ 解决方案 #

WITH split_data AS (
    SELECT 
        TRIM(TRAILING '|' FROM REGEXP_SUBSTR(your_string, '[^|]+', 1, seq)) AS segment
    FROM 
        (SELECT @str := 'c:\\-231.53 Gb-36.08 Gb-19.58|231.53 Gb-36.08 Gb-1.58|231.53 Gb-36.08 Gb-9.58|') AS str_tbl
    JOIN 
        (SELECT 1 AS seq UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS seq_tbl
    ON 
        REGEXP_SUBSTR(@str, '[^|]+', 1, seq) IS NOT NULL
    WHERE 
        TRIM(TRAILING '|' FROM REGEXP_SUBSTR(@str, '[^|]+', 1, seq)) != ''
),
extracted_values AS (
    SELECT 
        CAST(SUBSTRING(segment, LOCATE('-', segment, LOCATE('-', segment) + 1) + 1) AS DECIMAL(10,2)) AS num
    FROM 
        split_data
)
SELECT 
    CONCAT(SUM(CASE WHEN num < 10 THEN 1 ELSE 0 END), '/', COUNT(*)) AS result
FROM 
    extracted_values;

SQL Server 2016+ 解决方案 #

DECLARE @str NVARCHAR(MAX) = 'c:\-231.53 Gb-36.08 Gb-19.58|231.53 Gb-36.08 Gb-1.58|231.53 Gb-36.08 Gb-9.58|';

WITH split_data AS (
    SELECT value AS segment
    FROM STRING_SPLIT(@str, '|')
    WHERE value != ''
),
extracted_values AS (
    SELECT 
        CAST(RIGHT(segment, LEN(segment) - CHARINDEX('-', segment, CHARINDEX('-', segment) + 1)) AS DECIMAL(10,2)) AS num
    FROM split_data
)
SELECT CONCAT(SUM(CASE WHEN num < 10 THEN 1 ELSE 0 END), '/', COUNT(*)) AS result
FROM extracted_values;

逻辑说明 #

1. 拆分字符串：将原始字符串按|分割为独立行，过滤掉末尾空分段（因原字符串以|结尾）。
2. 提取目标数值：每个分段格式为xxx-xxx-数值，通过定位第二个-的位置，截取后续内容并转换为数值类型。
3. 统计计算：用条件计数得到小于10的数值数量，结合总数量拼接成x/y的结果格式。

示例运行结果为2/3，符合需求。

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