如何从另一个表中获得一个表中的最高外观值

Question

所以我有3个表参考汽车，保证和事故。与其他人相比，我想知道事故最多的车辆品牌。

我已经尝试了很多方法，但是大多数情况下，我只能获得或所有返回的品牌，或者是注册最多的汽车品牌，而不是最多的事故

这些是我的桌子

create table car(
    n_veic bigint not null,
    matric varchar(15) not null,
    pais_matric text not null,
    n_pess bigint not null,
    tipo text not null,
    cor text not null,
    brand text not null,
    modelo varchar(15),
    primary key(n_veic),
    unique(matric),
    foreign key (n_pess) references pessoa(n_pess)
);
create table ensurance(
    apolice bigint not null,
    segurado bigint not null,
    car bigint not null,
    datai date not null,
    dataf date not null,
    cobertura numeric(10,2) not null,
    primary key(apolice),
    unique(segurado, veiculo),
    foreign key (segurado) references pessoa(n_pess),
    foreign key (car) references car(n_veic)
);  
create table accident(
    n_acid bigint not null,
    pess_segura bigint not null,
    veic_seguro bigint not null,
    data date not null,
    local varchar(255) not null,
    descr text not null,
    primary key(n_acid),
    unique(n_acid, veic_seguro),
    foreign key (pess_segura,veic_seguro) references ensurance(segurado, car)

这就是我尝试的

SELECT marca
FROM veiculo NATURAL JOIN acidente
GROUP BY marca
HAVING count (distinct n_veic)>=ALL
    (SELECT count (distinct n_veic)
    FROM veiculo NATURAL JOIN acidente
    GROUP BY marca);

Answer 1

我认为逻辑是：

select c.marca, count(*) as num_acidentes
from acidente a join
     car c
     on a.veic_seguro = c.n_veic
group by c.marca
order by num_acidentes desc;

您可以使用fetch first 1 row only或适合数据库的任何内容 - 仅获取一行。

Answer 2

尝试这个 -

注意：1.尝试避免自然加入并使用特定的列参考。2.重新考虑与数量的不同是真正的必要性。

SELECT TOP 1 marca, COUNT(DISTINCT n_veic) 
FROM veiculo 
NATURAL JOIN acidente 
GROUP BY marca
ORDER BY COUNT(DISTINCT n_veic) DESC