MySQL GroupBy并水平显示

问题描述

假设我有下表:

1) tblScore

============================ 
Date      VendorID     Score 
============================ 
12/09/01  12001        A     
12/09/01  12001        A     
12/09/01  12002        B     
12/09/02  12003        C     
12/09/02  12003        A     
12/09/03  12001        C     
============================ 

我有这个查询:

SELECT ts.VendorID, ts.Score, COUNT(*)
FROM trxscore ts
GROUP BY ts.VendorID, ts.Score
ORDER BY ts.VendorID, ts.Score

但是如何显示如下表:

===========================
VendorID    A    B    C
===========================
12001       2    0    1
12002       0    1    0 
12003       1    0    1
===========================

而且，是否可以从文本中获取平均值?即VendorID 12001应该得到A的平均值.谢谢...

And, is it possible to get an average from a text? i.e., VendorID 12001 should get the average of A. Thanks...

推荐答案

尝试一下，

SELECT  VendorID,
        SUM(CASE WHEN Score = 'A' THEN 1 ELSE 0 END) totalA,
        SUM(CASE WHEN Score = 'B' THEN 1 ELSE 0 END) totalB,
        SUM(CASE WHEN Score = 'C' THEN 1 ELSE 0 END) totalC
FROM    tableName
GROUP BY VendorID

这篇关于MySQL GroupBy并水平显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！