如何编写MySQL查询实现两表内联并将行数据转为列

需求说明 #

• 两张表需执行INNER JOIN操作
• 仅能读取数据库，不能用DECLARE、SET或创建自定义函数
• 要把Table1的Car Part行数据转成多列（比如Car Part 1、Car Part 2这类）
• 最终结果行数必须和Table2一致，保证基于Table2的计算准确

示例表结构与测试数据 #

Table1（汽车零件表） #

CREATE TABLE Table1 (
    CarID INT,
    CarPart VARCHAR(50)
);

INSERT INTO Table1 VALUES
(1, 'Engine'),
(1, 'Transmission'),
(2, 'Brakes'),
(2, 'Suspension'),
(2, 'Tires');

Table2（汽车主信息表） #

CREATE TABLE Table2 (
    CarID INT,
    CarModel VARCHAR(50),
    ProductionYear INT
);

INSERT INTO Table2 VALUES
(1, 'Sedan', 2020),
(2, 'SUV', 2021);

实现代码 #

用窗口函数给每个车辆的零件编号，再通过条件聚合转列，最后和Table2内连接：

SELECT
    t2.CarID,
    t2.CarModel,
    t2.ProductionYear,
    MAX(CASE WHEN rn = 1 THEN t1.CarPart END) AS `Car Part 1`,
    MAX(CASE WHEN rn = 2 THEN t1.CarPart END) AS `Car Part 2`,
    MAX(CASE WHEN rn = 3 THEN t1.CarPart END) AS `Car Part 3`
    -- 要是零件数量更多，继续加对应的CASE语句就行
FROM Table2 t2
INNER JOIN (
    SELECT
        CarID,
        CarPart,
        ROW_NUMBER() OVER (PARTITION BY CarID ORDER BY CarPart) AS rn
    FROM Table1
) t1 ON t2.CarID = t1.CarID
GROUP BY t2.CarID, t2.CarModel, t2.ProductionYear;

预期查询结果 #

关键说明 #

• ROW_NUMBER()给每个CarID下的零件按顺序编号，避免重复或乱序
• 条件聚合MAX(CASE...)把行数据转成对应列，没有零件的位置自动填NULL
• 分组字段严格对应Table2的唯一标识，确保结果行数和Table2完全一致
• 全程只用基础SQL语法，不需要变量或自定义函数，适配只读权限环境

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