php - 如何计算MySQL的枚举数据

所有请帮助我..
我有这样的桌子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。
谢谢

最佳答案

您可以使用此：

  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