sqlite - 如何在计数中显示0？

Question

没有资格匹配怎么显示0？

我当前的查询是:

select s.sname, count(*) as number
from sailor s, boat b
where s.rating >= b.rating
group by s.sname
order by number desc;

我得到的结果是:

a  3
b  3
c  2

然而，这并不是我想要实现的。我想这样显示结果:

a 3
b 3
c 2
d 0
e 0

我尝试将 count() 更改为 isnull(count(), 0) 或 coalesce(count(*), 0) 但在这种情况下它不正确。

CREATE TABLE sailor (
    sname   character varying primary key,
    rating  integer
);

create table boat (
    bname   character varying primary key,
    color   character varying,
    rating  integer
);

Answer 1

这可能对你有帮助

select s.sname,(select count(*) 
from boat b
where s.rating >= b.rating
) t
from sailor s
order by t desc;

SQLFIDDLE