SQL:外连接和计数

Question

我必须创建一个 sql 语句来显示羊名和他/她的 sibling 数量。

这是我的sql脚本:

create table breeder (
breederNumber   number(3) primary key,
name            varchar2(20),
residence       varchar2(20),
password        varchar2(20)
);

create table race (
raceCode        varchar2(2) primary key,
raceName        varchar2(20) not null
);

create table sheep (
earTagNumber    number(2) primary key,
father          number(2),
mother          number(2),
raceCode        varchar2(2),
name            varchar2(20),
previousBreeder number(3),
genotype        varchar2(7),
gender          varchar2(1) check (gender = 'M' OR gender = 'F'),

constraint fk_previousBreeder foreign key (previousBreeder) references breeder(breederNummer) on delete cascade,
constraint rk_raceCode foreign key (raceCode) references race(raceCode) on delete cascade,
constraint fk_mother foreign key (mother) references sheep(earTagNumber) on delete cascade,
constraint fk_father foreign key (father) references sheep(earTagNumber) on delete cascade
);

The outcome should look like this

我应该使用外连接，但我不知道 sql 语句应该是什么样子。

谁能帮帮我？

提前致谢!

Answer 1

select s1.earTagNumber, max(s1.name), count(s2.earTagNumber) 
 from sheep s1
 left join sheep s2 on (s1.father=s2.father) or (s1.mother=s2.mother)
group by s1.earTagNumber