具有多列的mysql多计数

Question

我设计了一个组织表格，如下所示：

Name          Supervisor     Manager
David            -            -
Josseph         David         -
Jenny           Josseph      David 
Step            Josseph      David
Mike            Josseph      David
steven          David

上面的图表解释说，大卫在组织的最高位置，约瑟夫和史蒂文向大卫汇报；珍妮、step和迈克向约瑟夫汇报。

我想知道有多少人向大卫汇报，大卫是多少人的经理？这是我想要的桌子：

Supervisor    Manager
2            3

只需要一个查询就能完成吗？

Answer 1

http://sqlfiddle.com/#!9/f39ce/2

SELECT SUM(IF(Supervisor='David',1,0)),
  SUM(IF(manager='David',1,0))
FROM mytable

下面是一个分组查询的例子，如果您需要的话：

http://sqlfiddle.com/#!9/bdd738/5

SELECT t1.name,
  SUM(IF(t1.name=t2.Supervisor,1,0)),
  SUM(IF(t1.name=t2.manager,1,0))
FROM mytable t1
LEFT JOIN mytable t2
ON t1.name = t2.Supervisor
  OR t1.name = t2.manager
GROUP BY t1.name;

Answer 2

它可以：

select Name as BossName,
sum( case when Supervisor = Name then 1 else 0 end) as Supervises,
sum( case when Manager = Name then 1 else 0 end) as Manages
 from your_table group by Name;

这会让你感觉到：

BossName Supervises    Manages

David    2             3
Josseph  3             0
Jenny    0             0
....     0             0

Answer 3

使用以下查询

select name,(select count(*) from <table> where supervisor=name) as supervisor,
(select count(*) from <table> where manager=name) as manager    
from <table>