mysql - 如何按计数和类别获取数据

Question

如果我有如下表格

Person   |   Category    | Fav Colors
-----    | -----------   | ------
 one     |   Native      |  RED
 one     |   Native      |  GREEN
 two     |   Non-Native  |  RED
 two     |   Non-Native  |  BLUE
 three   |   Native      |  RED
 three   |   Native      |  GREEN

如何根据每种颜色的每个类别中的人数获取数据？

即

Fav Color | Native  | Non-Native
--------- | --------| --------
RED       | 2       | 1
GREEN     | 2       | 0
BLUE      | 0       | 1

Answer 1

你可以这样做:

select   `Fav Colors` as `Fav Color`,
         count(case category when 'Native' then 1 end) Native,
         count(case category when 'Non-Native' then 1 end) `Non-Native`
from     tbl
group by `Fav Colors`

SQL Fiddle

注意:您没有提到排序顺序的任何要求。如果您需要一个，您可以只在 order by 子句中列出列号:

order by 2 desc