按foreach行分组时的Mysql案例

Question

数据库产品_api:

SHOPID PRODUCT_NAME PRODUCT_CODE  
1      prod_name1     SKU1  
2      prod_name2     SKU1  
3      prod_name3     SKU1  

我想按 PRODUCT_CODE 分组，并为每个 SHOPID 获取 PRODUCT_NAME；
我的代码:

SELECT case when SHOPID = 1 then PRODUCT_NAME end as shopid1,  
case when SHOPID = 2 then PRODUCT_NAME end as shopid2,  
case when SHOPID = 3 then PRODUCT_NAME end as shopid3,PRODUCT_CODE  
FROM products_api   
GROUP BY PRODUCT_CODE

结果应该是一行:

shopid1    shopid2    shopid3    PRODUCT_CODE  
prod_name1 prod_name2 prod_name3 SKU1 

但结果是一行:

shopid1    shopid2    shopid3    PRODUCT_CODE  
prod_name1                       SKU1 

谢谢。

Answer 1

你几乎让它工作了。只需要在 CASE 表达式周围添加 MAX 聚合函数:

SELECT MAX(case when SHOPID = 1 then PRODUCT_NAME end) as shopid1,  
       MAX(case when SHOPID = 2 then PRODUCT_NAME end) as shopid2,  
       MAX(case when SHOPID = 3 then PRODUCT_NAME end) as shopid3,
       PRODUCT_CODE  
FROM products_api   
GROUP BY PRODUCT_CODE

Demo here