SQLite中按重复OrderID分组 保留其他列并拼接ProductName

要实现按OrderID分组，同时将同一订单下的所有ProductName用逗号分隔完整展示，核心是使用字符串聚合函数。不同数据库的函数语法略有差异，以下是适配你查询语句的改造方案：

MySQL 版本 #

使用GROUP_CONCAT()函数拼接产品名称，同时确保GROUP BY子句包含所有非聚合列（同一OrderID对应的员工、订单日期等字段是唯一的，可直接纳入分组）：

SELECT 
    od.EmployeeID, 
    nv.LastName, 
    nv.FirstName, 
    od.OrderDate, 
    od.ShipperID, 
    od.OrderID,
    GROUP_CONCAT(prod.ProductName SEPARATOR ', ') AS ProductNames,
    SUM(ord.Quantity) AS TotalQuantity  -- 可选：统计该订单总商品数量
FROM Orders AS od
INNER JOIN Employees AS nv ON od.EmployeeID = nv.EmployeeID
INNER JOIN Shippers AS ship ON od.ShipperID = ship.ShipperID
INNER JOIN OrderDetails AS ord ON od.OrderID = ord.OrderID
INNER JOIN Products AS prod ON prod.ProductID = ord.ProductID
WHERE od.EmployeeID = '8' AND DATE(od.OrderDate) >= DATE('1997-01-01') AND ship.ShipperID = '2'
GROUP BY od.OrderID, od.EmployeeID, nv.LastName, nv.FirstName, od.OrderDate, od.ShipperID;

• GROUP_CONCAT(prod.ProductName SEPARATOR ', ')：将同一订单下的所有产品名称用「逗号+空格」分隔拼接成单个字符串
• 需注意：MySQL默认对GROUP_CONCAT的结果长度有限制，若产品数量较多，可通过SET group_concat_max_len = 10240;临时调整长度上限

PostgreSQL 版本 #

使用STRING_AGG()函数实现聚合拼接：

SELECT 
    od.EmployeeID, 
    nv.LastName, 
    nv.FirstName, 
    od.OrderDate, 
    od.ShipperID, 
    od.OrderID,
    STRING_AGG(prod.ProductName, ', ') AS ProductNames,
    SUM(ord.Quantity) AS TotalQuantity
FROM Orders AS od
INNER JOIN Employees AS nv ON od.EmployeeID = nv.EmployeeID
INNER JOIN Shippers AS ship ON od.ShipperID = ship.ShipperID
INNER JOIN OrderDetails AS ord ON od.OrderID = ord.OrderID
INNER JOIN Products AS prod ON prod.ProductID = ord.ProductID
WHERE od.EmployeeID = '8' AND od.OrderDate >= '1997-01-01' AND ship.ShipperID = '2'
GROUP BY od.OrderID, od.EmployeeID, nv.LastName, nv.FirstName, od.OrderDate, od.ShipperID;

SQL Server 版本 #

SQL Server 2017及以上版本 #

直接使用STRING_AGG()函数：

SELECT 
    od.EmployeeID, 
    nv.LastName, 
    nv.FirstName, 
    od.OrderDate, 
    od.ShipperID, 
    od.OrderID,
    STRING_AGG(prod.ProductName, ', ') AS ProductNames,
    SUM(ord.Quantity) AS TotalQuantity
FROM Orders AS od
INNER JOIN Employees AS nv ON od.EmployeeID = nv.EmployeeID
INNER JOIN Shippers AS ship ON od.ShipperID = ship.ShipperID
INNER JOIN OrderDetails AS ord ON od.OrderID = ord.OrderID
INNER JOIN Products AS prod ON prod.ProductID = ord.ProductID
WHERE od.EmployeeID = '8' AND CONVERT(DATE, od.OrderDate) >= '1997-01-01' AND ship.ShipperID = '2'
GROUP BY od.OrderID, od.EmployeeID, nv.LastName, nv.FirstName, od.OrderDate, od.ShipperID;

SQL Server 2016及更早版本 #

使用STUFF结合FOR XML PATH的方式实现拼接：

SELECT DISTINCT
    od.EmployeeID, 
    nv.LastName, 
    nv.FirstName, 
    od.OrderDate, 
    od.ShipperID, 
    od.OrderID,
    STUFF((
        SELECT ', ' + prod2.ProductName
        FROM OrderDetails AS ord2
        INNER JOIN Products AS prod2 ON prod2.ProductID = ord2.ProductID
        WHERE ord2.OrderID = od.OrderID
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ProductNames,
    (SELECT SUM(ord2.Quantity) FROM OrderDetails AS ord2 WHERE ord2.OrderID = od.OrderID) AS TotalQuantity
FROM Orders AS od
INNER JOIN Employees AS nv ON od.EmployeeID = nv.EmployeeID
INNER JOIN Shippers AS ship ON od.ShipperID = ship.ShipperID
INNER JOIN OrderDetails AS ord ON od.OrderID = ord.OrderID
INNER JOIN Products AS prod ON prod.ProductID = ord.ProductID
WHERE od.EmployeeID = '8' AND CONVERT(DATE, od.OrderDate) >= '1997-01-01' AND ship.ShipperID = '2';

内容的提问来源于stack exchange，提问作者Blog Khóa Học