MySQL如何将单表中不同Item_Mode的数据拆分至两列展示？

场景说明 #

先创建示例表并插入测试数据：

CREATE TABLE table_test(
ID INT,
Item VARCHAR(100),
Amount DOUBLE,
Item_Mode VARCHAR(10)
);

插入示例数据：

INSERT INTO `table_test`(`ID`,`Item`,`Amount`,`Item_Mode`) VALUES
(1,'Allowances',100.00,'a'),
(2,'HRA',200.00,'a'),
(3,'DA',100.00,'a'),
(4,'FBP',100.00,'a'),
(5,'Income Tax',50.00,'d'),
(6,'Insurance ',10.00,'d'),
(7,'Provident Fund',20.00,'d');

原表数据如下：

需求描述 #

需要将Item_Mode='a'的数据展示在左侧列区域，Item_Mode='d'的数据展示在右侧列区域，最终预期效果如下：

问题分析 #

你尝试的查询语句存在多处错误：表名引用格式错误（应写为table_test A而非Table A）、字段名错误（原表无Mode字段，应为Item_Mode），且关联条件和过滤逻辑不符合需求，导致结果异常：

SELECT A.Item AS EARN,B.Item AS DEDUCT
FROM Table A INNER JOIN TABLE B
ON A.ID = B.ID  WHERE MOD(A.ID,2)=1 AND A.Mode = 'a' OR B.Mode = 'd'

解决方案 #

通过**窗口函数ROW_NUMBER()**分别为两类数据生成自增序号，再以序号为关联条件做左连接，即可实现数据的左右对齐展示：

WITH 
earnings AS (
    SELECT 
        ID, Item, Amount, Item_Mode,
        ROW_NUMBER() OVER (ORDER BY ID) AS rn
    FROM table_test
    WHERE Item_Mode = 'a'
),
deductions AS (
    SELECT 
        ID, Item, Amount, Item_Mode,
        ROW_NUMBER() OVER (ORDER BY ID) AS rn
    FROM table_test
    WHERE Item_Mode = 'd'
)
SELECT 
    e.ID AS IDA, e.Item AS ItemA, e.Amount AS AmountA, e.Item_Mode AS Item_ModeA,
    d.ID AS IDB, d.Item AS ItemB, d.Amount AS AmountB, d.Item_Mode AS Item_ModeB
FROM earnings e
LEFT JOIN deductions d ON e.rn = d.rn
ORDER BY e.rn;

原理说明 #

1. 用WITH子句拆分出两类数据：earnings筛选Item_Mode='a'的记录，deductions筛选Item_Mode='d'的记录，同时用ROW_NUMBER()按ID排序生成序号，保证两类数据的顺序匹配
2. 使用LEFT JOIN关联两个子查询，确保即使'a'类数据数量多于'd'类，所有'a'数据都会被完整展示，不足的'd'类字段自动填充为NULL
3. 最终按序号排序，实现预期的左右列对齐效果

内容的提问来源于stack exchange，提问作者Sen K Mathew