DB2 SQL实现基于另一表指定位置批量统计学生物品数量

核心思路 #

不用逐个写SUBSTRING硬编码位置，而是通过生成数字序列拆分长字符串，逐个提取每个位置的字符（跳过X空白），关联物品表匹配名称后，按学生+物品分组统计数量，适配任意长度的字符串。

MySQL 实现方案 #

利用递归CTE生成每个字符的位置，拆分字符串后关联统计：

WITH RECURSIVE split_strings AS (
    SELECT 
        Student,
        1 AS pos,
        SUBSTRING(item_code_str, 1, 1) AS code
    FROM Table2
    UNION ALL
    SELECT 
        Student,
        pos + 1,
        SUBSTRING(item_code_str, pos + 1, 1) AS code
    FROM split_strings
    WHERE pos < LENGTH(item_code_str)
)
SELECT 
    ss.Student,
    t1.items,
    COUNT(*) AS item_count
FROM split_strings ss
JOIN Table1 t1 ON ss.code = t1.ids
WHERE ss.code != 'X'
GROUP BY ss.Student, t1.items
ORDER BY ss.Student, t1.items;

• 递归CTE从位置1开始，逐个截取字符串的每个字符，直到遍历完整个字符串
• 过滤掉X后，关联Table1匹配物品名称
• 最后按学生和物品分组，统计每个学生的各物品持有数量

SQL Server 实现方案 #

用系统表生成足够多的数字序列（覆盖最长字符串长度），拆分字符串后统计：

WITH nums AS (
    SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
)
SELECT 
    t2.Student,
    t1.items,
    COUNT(*) AS item_count
FROM Table2 t2
JOIN nums n ON n.n <= LEN(t2.item_code_str)
CROSS APPLY (
    SELECT SUBSTRING(t2.item_code_str, n.n, 1) AS code
) c
JOIN Table1 t1 ON c.code = t1.ids
WHERE c.code != 'X'
GROUP BY t2.Student, t1.items
ORDER BY t2.Student, t1.items;

• nums CTE生成1到10000的数字序列（可根据实际字符串长度调整TOP值）
• 通过JOIN匹配每个字符位置，用SUBSTRING提取对应字符
• 过滤空白X后关联物品表，分组统计数量

通用优化建议 #

1. 如果数据库支持内置的单字符拆分函数（如PostgreSQL的STRING_TO_ARRAY配合UNNEST），可直接用内置函数替代递归/数字表，性能更优
2. 字符串长度特别大时，优先用预生成的数字表（而非递归CTE），避免递归层级过多导致性能下降
3. 可给Table1.ids、Table2.Student建立索引，提升关联和分组的效率

内容的提问来源于stack exchange，提问作者QA Ninja 2137