mysql - 如何使用 group by 而不丢失非空详细信息

Question

SELECT item_id,
 (CASE WHEN cat_level="1" THEN cat_name ELSE "" END) AS category,
 (CASE WHEN cat_level="2" THEN cat_name ELSE "" END) AS sub_cat,
FROM item

结果:

|  item id  |   category    |    sub_cat   |
|    01     |transportation |              |
|    01     |               |     car      |

当我添加group by item_id时，结果变为:

|  item id  |   category    |    sub_cat   |
|    01     |transportation |              |

但是如何显示以下内容呢？

|  item id  |   category    |    sub_cat   |
|    01     |transportation |     car      |

Answer 1

使用聚合函数，例如 Max(并从 CASE 中删除 ELSE ""):

SELECT   item_id,
         Max(CASE WHEN cat_level="1" THEN cat_name END) AS category,
         Max(CASE WHEN cat_level="2" THEN cat_name END) AS sub_cat,
FROM     item
GROUP BY item_id