如何将同一订单的多件商品合并至单条数据库记录？

Hey there! Let's figure out how to combine all products from the same order into a single row with separate product columns, just like you want. Here's what you can do, depending on your database system:

Core Idea #

The key here is to pivot your data: instead of having one product per row for an order, we'll turn each unique product in the order into its own column. We can either do this with fixed columns (if you know the maximum number of products per order) or dynamically generate columns (if the number varies).

First, let's assume you have a view (let's call it OrderTransactions) with this structure (matching your current format):

OrderID | Occupation       | Age | Product Name
--------|------------------|-----|--------------
300     | Network Analyst  | 33  | Switches
300     | Network Analyst  | 33  | Hp Laptop

Fixed Column Solution (Works for Most Databases) #

If you know the maximum number of products per order (say, 2 in your example), use conditional aggregation. This method is straightforward and works in SQL Server, MySQL, PostgreSQL, and more:

SELECT
  OrderID,
  Occupation,
  Age,
  -- Grab the first product in the order
  MAX(CASE WHEN product_rank = 1 THEN "Product Name" END) AS "Product Name 1",
  -- Grab the second product (add more lines if you have more products)
  MAX(CASE WHEN product_rank = 2 THEN "Product Name" END) AS "Product Name 2"
FROM (
  -- Assign a rank to each product within its order
  SELECT
    OrderID,
    Occupation,
    Age,
    "Product Name",
    ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY "Product Name") AS product_rank
  FROM OrderTransactions
) AS ranked_products
GROUP BY OrderID, Occupation, Age;

This will give you exactly the output you want:

OrderID | Occupation       | Age | Product Name 1 | Product Name 2
--------|------------------|-----|----------------|----------------
300     | Network Analyst  | 33  | Switches       | Hp Laptop

Dynamic Column Solution (For Variable Product Counts) #

If you don't know how many products an order might have, you'll need dynamic SQL to generate columns on the fly. Here's how to do it for common databases:

SQL Server #

DECLARE @column_list AS NVARCHAR(MAX),
        @final_query AS NVARCHAR(MAX);

-- Generate the list of product columns (Product Name 1, Product Name 2, etc.)
SET @column_list = STUFF((SELECT DISTINCT ',' + QUOTENAME('Product Name ' + CAST(product_rank AS VARCHAR(10)))
                          FROM (
                            SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY "Product Name") AS product_rank
                            FROM OrderTransactions
                          ) ranked
                          FOR XML PATH(''), TYPE
                         ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- Build and run the dynamic pivot query
SET @final_query = 'SELECT OrderID, Occupation, Age, ' + @column_list + '
                    FROM (
                      SELECT
                        OrderID,
                        Occupation,
                        Age,
                        "Product Name",
                        ''Product Name '' + CAST(ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY "Product Name") AS VARCHAR(10)) AS column_name
                      FROM OrderTransactions
                    ) product_data
                    PIVOT (
                      MAX("Product Name")
                      FOR column_name IN (' + @column_list + ')
                    ) pivot_table';

EXECUTE(@final_query);

MySQL #

-- First, generate the column definitions
SET @column_defs = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    'MAX(CASE WHEN product_rank = ', product_rank, ' THEN "Product Name" END) AS `Product Name ', product_rank, '`'
  )
) INTO @column_defs
FROM (
  SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY "Product Name") AS product_rank
  FROM OrderTransactions
) ranked;

-- Build and execute the dynamic query
SET @final_query = CONCAT('SELECT OrderID, Occupation, Age, ', @column_defs, '
                          FROM (
                            SELECT
                              OrderID,
                              Occupation,
                              Age,
                              "Product Name",
                              ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY "Product Name") AS product_rank
                            FROM OrderTransactions
                          ) ranked_products
                          GROUP BY OrderID, Occupation, Age');

PREPARE stmt FROM @final_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL #

First, make sure you have the tablefunc extension enabled (it's needed for the crosstab function):

CREATE EXTENSION IF NOT EXISTS tablefunc;

Then run the pivot query:

SELECT *
FROM crosstab(
  -- Source query: assign ranks to products per order
  'SELECT OrderID, Occupation, Age, ''Product Name '' || row_number() over(partition by OrderID order by "Product Name"), "Product Name"
   FROM OrderTransactions
   ORDER BY 1, 4',
  -- List of columns to pivot into
  'SELECT DISTINCT ''Product Name '' || row_number() over(partition by OrderID order by "Product Name")
   FROM OrderTransactions
   ORDER BY 1'
) AS ct(OrderID INT, Occupation VARCHAR, Age INT, "Product Name 1" VARCHAR, "Product Name 2" VARCHAR);

Using Raw Tables Instead of a View #

If you want to skip the view and query the four original tables directly, replace OrderTransactions with this joined query:

SELECT
  o.OrderID,
  c.Occupation,
  c.Age,
  p."Product Name"
FROM "Order Table" o
JOIN "Ordered Product Table" op ON o.OrderID = op.OrderID
JOIN "Customer Table" c ON o.CustomerID = c.CustomerID
JOIN "Product Table" p ON op.ProductID = p.ProductID

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