php - MySQL如何统计枚举数据

Question

请大家帮帮我..我有这样的表格 e.q = table1

Name | status |
donez  open
donez  closed
donez  reopen
donez  closed
alex   open

我需要显示这样的数据:

    Name   |            Total                   |
-------------------------------------------------
    donez  | open = 1 | closed = 2 | reopen = 1 |
-------------------------------------------------
    alex   | open = 1 | closed = 0 | reopen = 0 |
-------------------------------------------------

请帮助我，名称类型 = Varchar，状态类型 = ENUM。谢谢

Answer 1

你可以使用这个:

  select name ,
  sum(CASE WHEN status = 'open' then 1 else 0 end )as total_open,
  sum(CASE WHEN status = 'closed' then 1 else 0 end) as total_closed,
  sum(CASE WHEN status = 'reopen' then 1 else 0 end )as total_reopen
  from table1
  group by name

DEMO HERE

如果您希望按照您的意愿排序结果，请在最后添加此内容。

 order by name desc