mysql - 如何计算成员数，使用别名将结果输出到分组表中？

Question

我正在尝试以如下格式输出具有基本成员(member)资格和 AAA 成员(member)资格的成员(member)数量

#BASIC      #AAA
----------- -----------
     43          18

这是我的代码:

select count(club) as #Basic, count(club) as #AAA 
from yrb_member
where club = 'Basic' 
group by club;

但我一直得到这样的输出:

#BASIC      #AAA
----------- -----------
     18          18
     43          43

我该如何纠正这个问题？

数据库数据都是varchars:

insert into yrb_member (club, cid) values
('Basic',1),

Answer 1

这几乎适用于所有 RDBMS

select 
  sum(CASE WHEN club='Basic' THEN 1 ELSE 0 END) as #Basic
, sum(CASE WHEN club='AAA' THEN 1 ELSE 0 END)   as #AAA 
from yrb_member
where club in ('Basic', 'AAA');

如果在 RDBMS 中可用 - 这看起来更好

select 
  count(*) FILTER (WHERE club='Basic') as #Basic
, count(*) FILTER (WHERE club='AAA')   as #AAA 
from yrb_member
where club in ('Basic', 'AAA');