如何编写SQL查询实现Orders表行转列及Left Join关联需求

嘿，我明白你想要把每个客户的所有订单和对应的银行账号都合并到同一行展示，对吧？这其实是典型的行转列需求，我给你几种不同场景下的解决方案，你可以根据自己用的数据库来选。

首先，我们需要先给每个客户的订单标记一个序号（比如第1个订单、第2个订单...），然后通过聚合或者拼接的方式，把同一个客户的所有订单信息“摊平”到同一行里。这个思路正好对应你提到的“left join all orders on left on 1 row”的关联逻辑——把每个客户的所有订单都关联到他们的唯一主行上。

如果你能确定每个客户最多有多少个订单（比如最多3个），可以用条件聚合的方式直接生成对应列，这种方法简单直观，性能也不错。

以SQL Server为例：

SELECT 
    ClientID,
    Name,
    Adres,
    -- 第1个订单和对应银行号
    MAX(CASE WHEN OrderSeq = 1 THEN OrderID END) AS Order1,
    MAX(CASE WHEN OrderSeq = 1 THEN BankNumber END) AS BankNumber1,
    -- 第2个订单和对应银行号
    MAX(CASE WHEN OrderSeq = 2 THEN OrderID END) AS Order2,
    MAX(CASE WHEN OrderSeq = 2 THEN BankNumber END) AS BankNumber2,
    -- 第3个订单和对应银行号，可按需继续扩展
    MAX(CASE WHEN OrderSeq = 3 THEN OrderID END) AS Order3,
    MAX(CASE WHEN OrderSeq = 3 THEN BankNumber END) AS BankNumber3
FROM (
    -- 子查询：给每个客户的订单按OrderID排序并标记序号
    SELECT 
        ClientID,
        Name,
        Adres,
        OrderID,
        BankNumber,
        ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY OrderID) AS OrderSeq
    FROM Orders
) AS OrderWithSeq
GROUP BY ClientID, Name, Adres
ORDER BY ClientID;

如果不需要把每个订单拆成单独的列，只是想把所有订单和银行号按格式拼接在一个字段里，用字符串聚合函数会更省事。

MySQL 版本 #

SELECT 
    ClientID,
    Name,
    Adres,
    -- 用空格分隔每个订单+银行号组合
    GROUP_CONCAT(CONCAT(OrderID, ' ', BankNumber) SEPARATOR ' ') AS OrderBankDetails
FROM Orders
GROUP BY ClientID, Name, Adres
ORDER BY ClientID;

SQL Server 2017+ 版本 #

SELECT 
    ClientID,
    Name,
    Adres,
    STRING_AGG(CONCAT(OrderID, ' ', BankNumber), ' ') AS OrderBankDetails
FROM Orders
GROUP BY ClientID, Name, Adres
ORDER BY ClientID;

这个方案的输出结果就是你示例里的格式：ClientID Adres Name order1 Banknumber1 order2 Banknumber2 ...，只是把所有订单信息放在一个字段里。

如果不确定每个客户最多有多少订单，没法预先写死CASE语句，可以用动态SQL自动生成对应数量的列。

以SQL Server为例：

-- 先获取所有客户中最多的订单数
DECLARE @maxOrderCount INT;
SELECT @maxOrderCount = MAX(OrderSeq)
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY OrderID) AS OrderSeq
    FROM Orders
) AS OrderSeqStats;

-- 动态拼接SQL语句
DECLARE @sql NVARCHAR(MAX) = N'
SELECT 
    ClientID,
    Name,
    Adres';

DECLARE @currentSeq INT = 1;
WHILE @currentSeq <= @maxOrderCount
BEGIN
    -- 为每个序号添加Order和BankNumber列
    SET @sql += N',
    MAX(CASE WHEN OrderSeq = ' + CAST(@currentSeq AS NVARCHAR) + N' THEN OrderID END) AS Order' + CAST(@currentSeq AS NVARCHAR) + N',
    MAX(CASE WHEN OrderSeq = ' + CAST(@currentSeq AS NVARCHAR) + N' THEN BankNumber END) AS BankNumber' + CAST(@currentSeq AS NVARCHAR);
    SET @currentSeq += 1;
END

-- 完成SQL语句拼接并执行
SET @sql += N'
FROM (
    SELECT 
        ClientID,
        Name,
        Adres,
        OrderID,
        BankNumber,
        ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY OrderID) AS OrderSeq
    FROM Orders
) AS OrderWithSeq
GROUP BY ClientID, Name, Adres
ORDER BY ClientID;';

EXEC sp_executesql @sql;

这个脚本会自动根据数据里的最大订单数生成对应的列，不管客户有多少订单都能适配。

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