SQL:查找拥有最多从未进球的球员的球队的国家名称

Question

我有以下表格:

create table Players (
id integer,
name varchar(50) not null,
birthday date,
memberOf integer not null,
position varchar(20).
primary key (id),
foreign key (memberOf) references Teams(id)
);

create table Goals (
id integer,
scoredIn integer not null,
scoredBy integer not null,
timeScored integer not null,
rating varchar(20),
primary key (id),
foreign key (scoredIn) references Matches(id),
foreign key (scoredBy) references Players(id)
);

create table Teams (
id integer,
country varchar(50) not null,
primary key (id)
);

我在上面的表格中有以下数据:

玩家:

id       |    name     |     birthday     |     memberof    |     position
7           Mina            1997-01-20         1                 Captain
9           John            1997-09-01         1                 Quarterback 
2           Minnie          1995-10-13         3                 Goalkeeper
13          Lisa            1997-03-27         4                 Captain 
12          Rina            1995-01-03         2                 Fullback
11          Jasper          2002-09-22         1                 Halfback
17          Rose            1997-02-11         1                 Goalkeeper
22          Parvin          1993-03-09         3                 Goalkeeper
25          Nasom           1996-12-29         3                 Fullback

目标:

id    |     scoredin    |    scoredby    |    timescored     |     rating
1           10               7                60                   amazing
2           10               7                30                   okay
3           10               7                90                   amazing
4           20               9                119                  nice
5           20               9                80                   amazing
6           20               9                75                   amazing
7           30               2                30                   nice 
8           30               2                90                   amazing
9           40               13               110                  amazing

团队:

id    |    country
1          Australia
2          Malaysia
3          Japan
4          Thailand

我正在尝试输出从未进球最多的球队的国家/地区名称。输出应该是:

Country     |     Players 
Australia         2
Japan             2

我有以下 View ，它给出了每个国家/地区从未进球的球员的数量:

create or replace view zerogoals as
select t.country, count(*)
from (
select distinct p.id, p.name, p.memberof, g.scoredby
from players p
full outer join goals g
on p.id = g.scoredby where scoredby is null
) s
inner join teams t on t.id = s.memberof group by t.country;

上面的查询给出了以下输出:

country    |     count
Australia        2
Japan            2
Malaysia         1

我尝试使用 max 函数来获得所需的输出:

select country, max(count)
from zerogoals
group by country;

但是我得到以下输出:

country    |    max
Australia       2
Japan           2
Malaysia        1

我不确定如何在 View zerogals 中获取属性计数最大值的元组。任何见解都值得赞赏。

Answer 1

您可以使用 CTE:

with cte as (
  select
    t.id, t.country, count(*) players 
  from teams t inner join (
    select * from players 
    where id not in (select scoredby from goals)
  ) p on p.memberOf = t.id
  group by t.id, t.country
)
select country, players 
from cte
where players = (select max(players) from cte)
order by country

参见 demo .
结果:

country   | players 
Australia |       2
Japan     |       2