sql - 选择计数查询

Question

我有两个表:

CREATE TABLE a_b
(
 a_b_id integer NOT NULL,
 name text NOT NULL
)

和

CREATE TABLE a
(
 a_b_id integer NOT NULL,
 status text NOT NULL
)

值是:

表a_b:

a_b_id   name 
  1      aaa
  2      bbb
  3      ccc

表a:

a_b_id   status
  1      ACTIVE
  1      ACTIVE
  1      ACTIVE
  1      DELETED
  2      DELETED

然后我尝试从表“a_b”中选择值，其中包含表“a”中没有“已删除”状态的相关值的计数。我正在尝试:

select ab.name, count(a.a_b_id) from a_b ab left join a a on ab.a_b_id=a.a_b_id
where a.status != 'DELETED' GROUP BY ab.a_b_id,ab.name

但实际结果是:

aaa   3

预期结果是:

aaa 3
bbb 0
ccc 0

那么我必须如何修改我的查询才能收到我预期的结果？

Answer 1

您的 where 子句将您的 left join 转换为 inner join。将条件放在连接的 on 子句中

select ab.name, 
       count(a.a_b_id) 
from a_b ab 
left join a a on ab.a_b_id = a.a_b_id
             and a.status != 'DELETED'
GROUP BY ab.a_b_id, ab.name