PostgreSQL中关联两表将同订单多行商品名合并查询方案

Hey there! Let's figure out how to generate that desired result where we group items by their order number and concatenate the item names into a single comma-separated string. Since different SQL database systems use different functions for string aggregation, I'll cover solutions for the most commonly used ones below:

MySQL/MariaDB #

For these databases, the GROUP_CONCAT() function is perfect for this task. We'll join the two tables on OrderNo to ensure we only include orders that exist in table1, then group and concatenate:

SELECT 
    t1.OrderNo,
    GROUP_CONCAT(t2.ItemName SEPARATOR ', ') AS ItemName
FROM table1 t1
INNER JOIN table2 t2 ON t1.OrderNo = t2.OrderNo
GROUP BY t1.OrderNo;

Note: The SEPARATOR ', ' ensures we have a space after each comma, matching your desired output.

SQL Server #

If you're using SQL Server 2017 or later, the built-in STRING_AGG() function simplifies this. For older versions, we'll use a combination of STUFF() and FOR XML PATH() to achieve the same result:

SQL Server 2017+ #

SELECT 
    t1.OrderNo,
    STRING_AGG(t2.ItemName, ', ') AS ItemName
FROM table1 t1
INNER JOIN table2 t2 ON t1.OrderNo = t2.OrderNo
GROUP BY t1.OrderNo;

Pre-2017 SQL Server #

SELECT 
    t1.OrderNo,
    STUFF(
        (SELECT ', ' + ItemName 
         FROM table2 t2 
         WHERE t2.OrderNo = t1.OrderNo 
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 
        1, 2, ''
    ) AS ItemName
FROM table1 t1
GROUP BY t1.OrderNo;

The STUFF() function removes the leading , that the XML path query adds by default.

Oracle #

Oracle uses the LISTAGG() function, which also lets us specify the order of the concatenated items (we'll use FID here to match the original order in table2):

SELECT 
    t1.OrderNo,
    LISTAGG(t2.ItemName, ', ') WITHIN GROUP (ORDER BY t2.FID) AS ItemName
FROM table1 t1
INNER JOIN table2 t2 ON t1.OrderNo = t2.OrderNo
GROUP BY t1.OrderNo;

The WITHIN GROUP (ORDER BY t2.FID) ensures items are listed in the same order they appear in table2 for each order.

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