mysql - 将多行 MySQL 行转换为多列和单行

Question

以下 MySQL 查询

SELECT
    `FBM_Orders`.`order-id`,
    `FBM_Orders`.`purchase-date`,
    `FBM_Orders`.`buyer-name`,
    `FBM_Orders`.`sku`,
    `IM_INV`.`LOC_ID`,
    `IM_INV`.`QTY_ON_HND`
FROM
    `FBM_Orders`
LEFT JOIN `IM_INV` ON `FBM_Orders`.`sku` = `IM_INV`.`ITEM_NO`
WHERE
    `FBM_Orders`.`quantity-to-ship` > 0
ORDER BY
    `FBM_Orders`.`purchase-date`,
    `IM_INV`.`LOC_ID` ASC;

产量

| order-id          |  purchase-date          |  buyer-name | sku   |LOC_ID|QTY_ON_HND|
|112-9548231-5585051|2017-09-11T21:34:27+00:00|Zacchary Motz|IE51198|AF    | 2        |
|112-9548231-5585051|2017-09-11T21:34:27+00:00|Zacchary Motz|IE51198|LO    | 1        |
|112-9548231-5585051|2017-09-11T21:34:27+00:00|Zacchary Motz|IE51198|S     | 1        |
|112-9548231-5585051|2017-09-11T21:34:27+00:00|Zacchary Motz|IE51198|SL    | 0        |

我浏览过有类似问题的帖子，但它们都使用 GROUP_CONCAT 并且得到的结果与我正在寻找的结果不同。我想知道如何得到结果

| order-id  | purchase-date  | buyer-name  | sku   | AF_QTY | LO_QTY | S_QTY  | SL_QTY |
|112-95482..|2017-09-11T21...|Zacchary Motz|IE51198|   2    |   1    |   1    |   0    |

谢谢

Answer 1

对于您想要的结果集，您可以使用基于 LOC_ID 的 QTY_ON_HND 条件总和

SELECT
    o.`order-id`,
    o.`purchase-date`,
    o.`buyer-name`,
    o.`sku`,
    SUM(CASE WHEN i.`LOC_ID` =  'AF' THEN i.QTY_ON_HND ELSE 0 END) AF_QTY,
    SUM(CASE WHEN i.`LOC_ID` =  'LO' THEN i.QTY_ON_HND ELSE 0 END) LO_QTY,
    SUM(CASE WHEN i.`LOC_ID` =  'S' THEN i.QTY_ON_HND ELSE 0 END) S_QTY,
    SUM(CASE WHEN i.`LOC_ID` =  'SL' THEN i.QTY_ON_HND ELSE 0 END) SL_QTY,
    i.`LOC_ID`,
    i.`QTY_ON_HND`
FROM
    `FBM_Orders` o
LEFT JOIN `IM_INV` i ON o.`sku` = i.`ITEM_NO`
WHERE
    o.`quantity-to-ship` > 0
GROUP BY o.`order-id`,
    o.`purchase-date`,
    o.`buyer-name`,
    o.`sku`
ORDER BY
    o.`purchase-date`