a*********u
发帖数: 1463 | 1 有3个表， cutomer （custid，custname, address） Product (Productid,
productname, category), sales (custid, productid)
现在要找出把所有产品都买过的客人，请问要怎么办啊
谢谢 |
B*****g
发帖数: 34098 | 2 http://www.w3schools.com/sql/sql_having.asp
【在 a*********u 的大作中提到】
: 有3个表， cutomer （custid，custname, address） Product (Productid,
: productname, category), sales (custid, productid)
: 现在要找出把所有产品都买过的客人，请问要怎么办啊
: 谢谢
|
g***l
发帖数: 18555 | 3 这个要PIVOT一下，你要填这个表
P1 P2 P3 P4 P5 ..PN
C1
C2
C3
.
CN. |
c**i
发帖数: 3 | 4 select custid
from sales
group by custid
having count(productid) = (select count(productid) from product) |
B*****g
发帖数: 34098 | 5 we can assume productid is pk in product table.
usually in sales table custid, productid is not unique.
【在 c**i 的大作中提到】
: select custid
: from sales
: group by custid
: having count(productid) = (select count(productid) from product)
|
g***l
发帖数: 18555 | 6 要做一个
SELECT DISTINCT CUSTID, PORDUCTID FROM SALE
才能用上面的QUERY |
a9
发帖数: 21638 | 7 到少要distinct一下吧，呵呵。
【在 c**i 的大作中提到】
: select custid
: from sales
: group by custid
: having count(productid) = (select count(productid) from product)
|
c**i
发帖数: 3 | 8
If custid, productid is not unique in sales table. Change the query to:
select custid
from sales
group by custid
having count(distinct productid) = (select count(productid) from product)
【在 B*****g 的大作中提到】
: we can assume productid is pk in product table.
: usually in sales table custid, productid is not unique.
|
y****w
发帖数: 3747 | 9 不用count，
select distinct custid from sale s1 where not exists ( select 1 from sale
s2 right join prod p on s2.pid = p.pid and s1.custid = s2.custid where s2.
pid is null) |
