SQL 选择不同

Question

有了这些数据

Team Name Won
X    Andy  1
X    Cara  1
X    Cara  1
X    Eric  0
X    Eric  0
X    Eric  0
X    Eric  0
Y    Bill  0
Y    Dave  0
Y    Dave  1
Y    Dave  1

我想要每支球队的球员人数和获胜者人数

Team Players Winners
X       3       2
Y       2       1

是否在两个查询中:

select team, count(distinct Name) as Players from test group by team
select team, count(distinct Name) as Winners from test where Won=1 group by team

但我无法计算出将其合二为一的语法。 TIA 为您提供帮助。约翰

Answer 1

您可以使用 case 进行条件聚合:

select team,
    count(distinct name) as Players,
    count(distinct case when won = 1 then name end) as winners
from test
group by team