如何对SQL单列内的值进行比较并编写条件语句实现行转列需求

问题原因 #

你原有写法存在两个核心问题：

1. ITEM_NUMBER<>ITEM_NUMBER是恒假条件，永远不会匹配到数据，因此所有2ND列返回NULL
2. 1ST列直接SUM了所有匹配规则的行，会把同类型的多个值加总，不符合拆分到不同列的需求

解决方案 #

核心思路是先给同分组内的同类型ITEM_NUMBER排序，按排序序号匹配对应列即可，以下是兼容绝大多数数据库的通用实现：

WITH ranked_items AS (
    SELECT 
        DATE,
        NAME,
        REFERENCE,
        TOTAL,
        -- 标记ITEM_NUMBER属于100类型还是50类型
        CASE WHEN ITEM_NUMBER LIKE '%-100-%' THEN '100'
             WHEN ITEM_NUMBER LIKE '%-50-%' THEN '50' END AS item_type,
        -- 同分组、同类型内排序，按末尾数字升序排列，得到1、2的序号
        ROW_NUMBER() OVER (
            PARTITION BY DATE, NAME, REFERENCE, 
                         CASE WHEN ITEM_NUMBER LIKE '%-100-%' THEN '100'
                              WHEN ITEM_NUMBER LIKE '%-50-%' THEN '50' END
            -- 不同数据库提取末尾数字的函数可以调整，比如Oracle用SUBSTR+INSTR，SQL Server用RIGHT
            ORDER BY CAST(SUBSTRING_INDEX(ITEM_NUMBER, '-', -1) AS UNSIGNED) ASC
        ) AS rn
    FROM 你的表名
)
SELECT 
    DATE,
    NAME,
    REFERENCE,
    MAX(CASE WHEN item_type = '100' AND rn = 1 THEN TOTAL END) AS 100_1ST,
    MAX(CASE WHEN item_type = '100' AND rn = 2 THEN TOTAL END) AS 100_2ND,
    MAX(CASE WHEN item_type = '50' AND rn = 1 THEN TOTAL END) AS 50_1ST,
    MAX(CASE WHEN item_type = '50' AND rn = 2 THEN TOTAL END) AS 50_2ND
FROM ranked_items
GROUP BY DATE, NAME, REFERENCE
ORDER BY DATE;

逻辑说明 #

• 先将所有ITEM_NUMBER按中间段（100/50）划分为不同类型组
• 同日期、同NAME、同REFERENCE、同类型的条目，按末尾数字从小到大排序得到序号
• 行转列时按序号匹配对应列，没有对应序号的条目自动返回NULL，完全符合你期望的输出格式
• 如果需要调整排序规则，比如要降序排列，修改ORDER BY后的排序方向即可

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