我试图获得购买了所有治疗套餐（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
我从上述声明中得出的结果使我返回购买了任何治疗套餐的客户。请帮忙？
PS-我不能同时显示给定的姓名和姓氏（SELECT c.givenname, c.familyname在第一行中），因为这会导致出现错误not a GROUP BY expression，请问为什么？
您可以尝试以下操作-使用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
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句