Mysql 在一个 Order by Case when...查询时选择并计数

Question

我有这个数据库表:membership

id   name       email               level      memberdate
1    Joe        joe@gmail.com       silver     04/09/2015
2    Peter      perter@gmail.com    bronze     05/09/2015
3    John       john@gmail.com      gold       06/09/2015
4    Jack       jack@gmail.com      bronze     09/09/2015

我首先使用此查询来显示级别=青铜的人员。

select * 
from membership 
order by 
  case 
    when level = 'bronze' then 1 
    else 2
  end,
  memberdate desc;

此外，我想计算在上述同一查询中有多少人的 level = bronze。在这种情况下，计数结果应为 2。我怎样才能做到这一点？

谢谢

Answer 1

select *, (select sum(level = 'bronze') from membership) as bronze_count
from membership 
order by case when level = 'bronze' then 1 else 2 end,
         memberdate desc