mysql - 在 SQL 中将行值显示为列值

Question

我在 SQL(XAMP) 中有这个表

item_name         category_name        category_id
----------------------------------------------------
Flash_disk        Accessories          1    
Fanta             Food                 2    
Printer           Accessories          1    
Sprite             Food                2    

我正在尝试使用此查询将category_name列值显示为列值，并且它显示得很好，如图所示。sql:

SELECT category_id, MAX(IF(`category_name` = "Food", item_name, NULL)) Food, MAX(IF(`category_name` = "Accessories", item_name, NULL)) Accessories
FROM categories
GROUP BY category_id

结果是

category_id         Food           Accessories
---------------------------------------------------
1                   NULL            Printer
2                   Sprite           NULL
---------------------------------------------------

有没有办法让所有列都填充值而不是显示空值结果是这样的:

category_id         Food           Accessories
---------------------------------------------------
1                   Fanta          Printer
2                   Sprite         Flash_disk   
---------------------------------------------------

Answer 1

这个预期结果没有多大意义。

例如，根据这些结果，为什么芬达的category_id为1？

但是这个查询几乎得到类似的结果:

SELECT rownum, 
MAX(CASE WHEN category_name = 'Food' THEN item_name END) AS Food, 
MAX(CASE WHEN category_name = 'Accessories' THEN item_name END) AS Accessories
FROM
(
    SELECT 
    MAX(cat1.category_name) AS category_name,
    COUNT(*) AS rownum,
    cat1.item_name
    FROM categories cat1
    JOIN categories cat2 ON cat2.category_id = cat1.category_id 
     AND cat2.item_name <= cat1.item_name
    GROUP BY cat1.category_id, cat1.item_name
) q
GROUP BY rownum
ORDER BY rownum;

返回

rownum  Food    Accessories
1       Fanta   Flash_disk
2       Sprite  Printer