数据透视表 MySQL？

穆阿马尔·胡迈迪

我有一个疑问：

SELECT item_id, 
       MAX(CASE WHEN `vendor_id` = 2 
                THEN price 
                END) AS "Bandung", 
       MAX(CASE WHEN `vendor_id` = 3 
                THEN price 
                END) AS "Vendor Bandung", 
       MAX(CASE WHEN `vendor_id` = 4 
                THEN price 
                END) AS "Jakarta", 
       MIN(price) 
FROM transactions 
GROUP BY item_id

输出：

我想从哪里获取有关价格的详细信息，如下所示

|ItemID|Bandung|Vendor Bandung|Jakarta |Price Min|*Area*
|10001 |12000  |11000         |9000    |9000     |*Jakarta|*
|10002 |12000  |11000         |12400   |11000    |*Vendor Bandung|*
|10004 |12000  |11000         |12400   |11000    |*Vendor Bandung|*
|10005 |12000  |11000         |12400   |11000    |*Vendor Bandung|*

他们不是

WITH cte AS (
SELECT item_id, 
       MAX(CASE WHEN vendor_id = 2 
                THEN price 
                END) AS Bandung, 
       MAX(CASE WHEN vendor_id = 3 
                THEN price 
                END) AS `Vendor Bandung`, 
       MAX(CASE WHEN vendor_id = 4 
                THEN price 
                END) AS Jakarta, 
       MIN(price) MinPrice
FROM transactions 
GROUP BY item_id
)
SELECT *,
       CASE MinPrice WHEN Bandung THEN 'Bandung'
                     WHEN `Vendor Bandung` THEN 'Vendor Bandung'
                     WHEN Jakarta THEN 'Jakarta'
                     ELSE 'unknown'
                     END Area
FROM cte

对于古代版本：

SELECT *,
       CASE MinPrice WHEN Bandung THEN 'Bandung'
                     WHEN `Vendor Bandung` THEN 'Vendor Bandung'
                     WHEN Jakarta THEN 'Jakarta'
                     ELSE 'unknown'
                     END Area
FROM 
(
SELECT item_id, 
       MAX(CASE WHEN vendor_id = 2 
                THEN price 
                END) AS Bandung, 
       MAX(CASE WHEN vendor_id = 3 
                THEN price 
                END) AS `Vendor Bandung`, 
       MAX(CASE WHEN vendor_id = 4 
                THEN price 
                END) AS Jakarta, 
       MIN(price) MinPrice
FROM transactions 
GROUP BY item_id
) cte

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。