mysql - 如何在 SELECT 语句中使用 SQL 值作为其列名？

Question

我有一个包含订单的表(每个订单一条记录)和一个包含订单详细信息的表(每个订单的产品一条记录)。我希望我的 SELECT 语句在以 ProductID 命名的列中返回每种产品的数量。这样我就不会明确地为每个 ProductID 编写查询，而是让查询适应它在 OrderDetails 表中找到的任何 ProductID。

结果会是这样的:

OrderID   TrackingNr   ProductID1   ProductID2   ProductID3
1         1Zetc        6            0            2
2         1Zetc2       0            1            1

我目前有以下内容:

SELECT Orders.OrderID AS OrderID,
TrackingNumbers.TrackingNumber AS TrackingNumber,
(SELECT Quantity FROM OrderDetails WHERE Orders.OrderID = OrderDetails.OrderID AND ProductCode = 'FPIA0009') AS FPIA0009,
(SELECT Quantity FROM OrderDetails WHERE Orders.OrderID = OrderDetails.OrderID AND ProductCode = 'FPIA0030') AS FPIA0030,
((SELECT Quantity FROM OrderDetails WHERE Orders.OrderID = OrderDetails.OrderID AND ProductCode = 'FPIA0060') +
(SELECT Quantity FROM OrderDetails WHERE Orders.OrderID = OrderDetails.OrderID AND ProductCode = 'FPIA0060xP')) AS FPIA0060,
(SELECT Quantity FROM OrderDetails WHERE Orders.OrderID = OrderDetails.OrderID AND ProductCode = 'FPIA0120') AS FPIA0120
FROM Orders, TrackingNumbers WHERE Orders.OrderID = TrackingNumbers.OrderID
ORDER BY OrderID, ShipDate

您可以看到，上面我明确要求每个特定 ProductID 的数量，但还有更多，我宁愿简化这一过程。

您还可以看到，我试图将两种不同产品的数量相加，因为它们来自同一库存，但由于某种原因，结果始终为空。

非常感谢您的帮助!

Answer 1

MySQL 没有 PIVOT 函数，但您可以使用聚合函数和 CASE 语句对其建模。对于动态版本，您将需要使用准备好的语句。

使用的示例表:

create table orders
(
  id int,
  name varchar(10)
);

insert into orders values
(1, 'Order 1'),
(2, 'Order 2'),
(3, 'Order 3'),
(4, 'Order 4');

create table orderdetails
(
  orderid int,
  productname varchar(20),
  qty int
);

insert into orderdetails values
(1, 'Product 1', 10),
(1, 'Product 2', 1),
(1, 'Product 3', 45),
(2, 'Product 1', 2),
(2, 'Product 5', 78),
(3, 'Product 4', 60),
(3, 'Product 2', 32),
(4, 'Product 1', 5),
(4, 'Product 3', 6),
(5, 'Product 2', 9),
(6, 'Product 4', 1);

create table trackingnumbers
(
  orderid int,
  trackingnumber varchar(10)
);

insert into trackingnumbers values
(1, '1Zetc'),
(2, '1Zdfghfdg'),
(3, '1Zkyui'),
(4, '1Zwe');

如果您知道想要作为列的值的数量，那么您可以像这样对值进行硬编码:

select o.id,
  t.trackingnumber,
  sum(case when productname = 'Product 1' then qty else 0 end) Product1,
  sum(case when productname = 'Product 2' then qty else 0 end) Product2,
  sum(case when productname = 'Product 3' then qty else 0 end) Product3,
  sum(case when productname = 'Product 4' then qty else 0 end) Product4,
  sum(case when productname = 'Product 5' then qty else 0 end) Product5
from orders o
left join orderdetails d
  on o.id = d.orderid
left join trackingnumbers t
  on o.id = t.orderid
group by o.id, t.trackingnumber;

参见 SQL Fiddle with Demo

但是如果你有一个未知数量的值，那么你可以使用准备好的语句:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when productname = ''',
      productname,
      ''' then qty else 0 end) AS ',
      replace(productname, ' ', '')
    )
  ) INTO @sql
FROM orderdetails;


SET @sql = CONCAT('SELECT o.id, t.trackingnumber, ', @sql, ' 
                  from orders o
                  left join orderdetails d
                    on o.id = d.orderid
                  left join trackingnumbers t
                    on o.id = t.orderid
                  group by o.id, t.trackingnumber');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参见 SQL Fiddle with Demo