如果结果包含其他表中的所有值，则派生结果

Question

我正在尝试获取已购买所有治疗包(A101、A102、A103(的客户的所有姓名

以下是我的表格说明：

客户

CustID Surname Given Name
100    White   Layla
101    Wells   Mary

账户

AcctNo CustID
600    100
601    101

AccountLine

AcctNo TreatmentNo
600    A102
601    A103
600    A102
600    A101
601    A101

处理

TreatmentNo Description
A101        For face
A102        For body
A103        For legs

在我的声明中，我把它写为：

SELECT c.givenname
FROM accountline al, treatment t, account a, customer c
WHERE al.treatmentno IN (SELECT treatmentno FROM treatment)
AND al.accountno IN (SELECT accountno FROM account)
AND c.custid IN (SELECT custid FROM customer)
GROUPBY c.givenname

我从上述声明中得出的结果是，我的客户购买了任何治疗包。请帮忙？

p.S-我不能同时显示给定的名字和姓氏(第一行中的SELECT c.givenname, c.familyname(，因为这会导致一个错误，说明not a GROUP BY expression，为什么会这样？

Answer 1

您可以尝试以下操作-使用having clause检查谁购买了的所有三种产品

SELECT c.givenname,c.familyname
FROM accountline al join treatment t on al.TreatmentNo=t.TreatmentNo
join account a on a.AcctNo=al.AcctNo
join customer c on c.CustID=a.CustID
where t.TreatmentNo in ('A101', 'A102', 'A103')
GROUP BY c.givenname,c.familyname
having count(distinct t.TreatmentNo)=3

Answer 2

以下是OVER和PARTITION BY函数的类似方法。

SELECT DISTINCT I.givenname, 
I.familyname
FROM (SELECT c.givenname,
c.familyname,
count(DISTINCT al.TreatmentNo) OVER (PARTITION BY al.AcctNo) as t_count
FROM accountline al join treatment t on al.TreatmentNo=t.TreatmentNo
join account a on a.AcctNo=al.AcctNo
join customer c on c.CustID=a.CustID
WHERE t.TreatmentNo in ('A101', 'A102', 'A103')) I
WHERE I.t_count = 3;