如何拆分单字段多零件数据并计算各国零件占比？SQL查询遇阻求助

问题分析 #

原SQL语句的核心问题：

• Parts_Used字段存储多个逗号分隔的零件编号，LIKE 'CH%'仅能匹配字段开头是CH的记录，无法识别字段中间或末尾的CH零件（比如IN_001,CH_002这类记录会被完全漏掉）。
• 原语句仅统计符合条件的机器总数，没有实现「每台机器内CH/IN零件占比」的计算需求。

分步解决方案 #

1. 拆分多零件字段 #

首先需要把Parts_Used中的多个零件编号拆分为单独的行，不同SQL方言的实现方式如下：

MySQL/MariaDB（8.0+版本）

用递归CTE生成序列配合字符串拆分：

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n <= 100 -- 覆盖单条记录最多零件数，可按需调整
)
SELECT 
    machine_id,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(Parts_Used, ',', n), ',', -1)) AS part
FROM tablename
CROSS JOIN nums
WHERE n <= 1 + LENGTH(Parts_Used) - LENGTH(REPLACE(Parts_Used, ',', ''));

PostgreSQL

利用string_to_array和unnest函数：

SELECT 
    machine_id,
    unnest(string_to_array(Parts_Used, ',')) AS part
FROM tablename;

SQL Server

使用内置STRING_SPLIT函数：

SELECT 
    machine_id,
    value AS part
FROM tablename
CROSS APPLY STRING_SPLIT(Parts_Used, ',');

2. 计算单台机器的零件占比 #

基于拆分后的零件数据，统计每台机器的CH/IN零件数量及占比：

WITH split_parts AS (
    -- 替换为对应SQL方言的拆分语句
    SELECT 
        machine_id,
        unnest(string_to_array(Parts_Used, ',')) AS part
    FROM tablename
)
SELECT 
    machine_id,
    COUNT(*) AS total_parts,
    SUM(CASE WHEN part LIKE 'CH%' THEN 1 ELSE 0 END) AS ch_parts,
    ROUND(SUM(CASE WHEN part LIKE 'CH%' THEN 1 ELSE 0 END)::FLOAT / COUNT(*) * 100, 2) AS ch_part_percentage,
    SUM(CASE WHEN part LIKE 'IN%' THEN 1 ELSE 0 END) AS in_parts,
    ROUND(SUM(CASE WHEN part LIKE 'IN%' THEN 1 ELSE 0 END)::FLOAT / COUNT(*) * 100, 2) AS in_part_percentage
FROM split_parts
GROUP BY machine_id;

3. 统计全量机器的整体零件占比 #

如果需要所有机器的CH/IN零件总占比，直接基于拆分后的汇总：

WITH split_parts AS (
    -- 替换为对应拆分语句
    SELECT 
        unnest(string_to_array(Parts_Used, ',')) AS part
    FROM tablename
)
SELECT 
    COUNT(*) AS total_parts,
    SUM(CASE WHEN part LIKE 'CH%' THEN 1 ELSE 0 END) AS ch_parts,
    ROUND(SUM(CASE WHEN part LIKE 'CH%' THEN 1 ELSE 0 END)::FLOAT / COUNT(*) * 100, 2) AS ch_part_percentage,
    SUM(CASE WHEN part LIKE 'IN%' THEN 1 ELSE 0 END) AS in_parts,
    ROUND(SUM(CASE WHEN part LIKE 'IN%' THEN 1 ELSE 0 END)::FLOAT / COUNT(*) * 100, 2) AS in_part_percentage
FROM split_parts;

额外建议 #

如果业务允许，建议重构数据表结构：将零件信息拆分为单独的关联表（比如machine_parts表，包含machine_id和part_id字段），彻底避免字段存储多值的设计，后续统计和维护会更高效。

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