假设我有下表:
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
===========================
并且,是否有可能从文本中获得平均值? IE。,VendorID 12001
应该得到A的平均值。谢谢...
尝试这个,
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
SQLFiddle 演示 http://sqlfiddle.com/#!2/1de17/2
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)