mysql - MySQL中如何将列名转移到结果集？

Question

我有一个疑问

 SELECT 
    count(CASE WHEN fan_gate.genre = 'Breaks' THEN fan_gate.id END) AS 'Breaks',
    count(CASE WHEN fan_gate.genre = 'Cill Out' THEN fan_gate.id END) AS 'Chill Out',
    count(CASE WHEN fan_gate.genre = 'Deep House' THEN fan_gate.id END) AS 'Deep House',
    count(CASE WHEN fan_gate.genre = 'Drum & Bass' THEN fan_gate.id END) AS 'Drum & Bass'
FROM fan_gate

当前结果集

Breaks  | Cill Out  |Deep House  |Drum & Bass
------------------------------------------------
  4581  |    785    | 7858       |   4474

我需要

Column 1    | column 2
-----------------------
Breaks      | 4581
Cill Out    |  785
Deep House  | 7858
Drum & Bass | 4474

Answer 1

您只想分组依据:

SELECT fg.genre, COUNT(*)
FROM fan_gate fg
GROUP BY fg.genre;

如果您只关心这四种类型，请在 GROUP BY 之前添加 WHERE 子句来选择您关心的类型。