选择在给定范围内具有子标的容器

Question

我有两张表：

项目：

ID	名称
1	ABC
2	DEF
3	GHI
4	JKL
5	MNO
6	PQR

附件：

ID	状态	ProjectID
1	A1	1
A1	1	2
3	A2	A1 1
A2 2 4	A2	2
4	3 6 A2	3
7	A2 2 5 A1 3 6 A2	A1
4	A1	4
8 A2 4	A2	4
9	A3	4
10	A1	5
11	A2	5
12	A4	5

我想获得的项目名称，该项目名称只有具有状态A1和A2的作业（必须两个），并且不会在状态A3和A4中进行分配。 因此，结果应该是：

GHI

我尝试的是：

select distinct 
p.Name 
from 
Attachment a 
inner join Project p on p.Id = a.ProjectId 
group by 
p.Name, a.status 
having (a.Status = 'a1' or a.Status = 'a2'

Answer 1

您可以使用存在|不存在关键字来实现这一目标。

select [Name] from Project t1
where 
    exists (select 1 from Attachment where [Status] = 'a1' and ProjectId = t1.Id)
and
    exists (select 1 from Attachment where [Status] = 'a2' and ProjectId = t1.Id)

and 
    not exists (select 1 from Attachment where [Status] = 'a3' and ProjectId = t1.Id)
and
    not exists (select 1 from Attachment where [Status] = 'a4' and ProjectId = t1.Id)

Answer 2

使用String_agg，subquery和join以获取所需的结果

SELECT name
FROM   (SELECT P.name,
               String_agg(status, ',')
                 within GROUP (ORDER BY status) Status
        FROM   project P
               join attachment A
                 ON A.projectid = P.id
        GROUP  BY P.name) t
WHERE  status = 'a1,a2'  

dbfiddle

Answer 3

您可以将计数与非在：

select P.name
from Project P
left join Attachment A on P.id = A.ProjectId
where p.id not in (select A2.ProjectId 
                   from Attachment A2 
                   where A2.status in ('a3', 'a4'))
and A.status in ('a1', 'a2')
group by P.name
having count(distinct A.status) = 2