MySQL 如何将第二组参数结果转换为列？

Question

以下MySQL查询结果集重复MerchantID:

select MerchantID,Category,SUM(Amount) 
group by MerchantID,Category 
from merchants

这将产生以下结果:

MerchantID | Category | Total |

1           | gold     | 450  |
1           | silver   | 600  |
2           | gold     | 1120 |
2           | bronze   | 200  |

我想得到如下结果集:

MerchantID | gold | silver | bronze |

1           | 450  | 600    | 0      |
2           | 1120 | 0      | 200    |

我尝试了很多查询，但无法得到这样的结果。请为我的问题提供解决方案。谢谢。

Answer 1

使用条件聚合:

select MerchantID, 
       SUM(CASE WHEN Category = 'gold' THEN Total ELSE 0 END) AS gold,
       SUM(CASE WHEN Category = 'silver' THEN Total ELSE 0 END) AS silver,
       SUM(CASE WHEN Category = 'bronze' THEN Total ELSE 0 END) AS bronze 
from merchants
group by MerchantID 

Demo here