在 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

Answer 2

您可以使用coalesce()：

SELECT category_id,
COALESCE(MAX(CASE WHEN `category_name` = 'Food' THEN  item_name END), 'Fanta') as Food, 
COALESCE(MAX(CASE WHEN `category_name` = 'Accessories' THEN  item_name END), 'Flash_disk') as Accessories
FROM categories
GROUP BY category_id;