问个SQL问题 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

问个SQL问题

问个SQL问题# Database - 数据库

mb2011-06-22 07:06

1 楼

☆─────────────────────────────────────☆
didochili (didochili) 于 (Fri Nov 21 17:01:58 2008) 提到:
刚听说我还不相信.刚买了个东西,果然马上CB就进PAYPAL帐号了.
找到了以下在fatwallet上的关于这个新规定:
For what it's worth, someone posted the following in SD's forum:eBay/Live
Search CashBack V2 was launched yesterday. eBay and PayPal will determine if
CashBack eligible purchases will receive either instant CashBack or will
need to go through the 60 day pending period. Microsoft will not have a say
in the decision. You will not know if t

a*u2011-06-22 07:06

2 楼

有3个表， cutomer （custid，custname, address） Product (Productid,
productname, category), sales (custid, productid)
现在要找出把所有产品都买过的客人，请问要怎么办啊
谢谢

B*g2011-06-22 07:06

3 楼

http://www.w3schools.com/sql/sql_having.asp

【在 a*********u 的大作中提到】

: 有3个表， cutomer （custid，custname, address） Product (Productid,
: productname, category), sales (custid, productid)
: 现在要找出把所有产品都买过的客人，请问要怎么办啊
: 谢谢

g*l2011-06-22 07:06

4 楼

这个要PIVOT一下，你要填这个表
P1 P2 P3 P4 P5 ..PN
C1
C2
C3
.
CN.

c*i2011-06-22 07:06

5 楼

select custid
from sales
group by custid
having count(productid) = (select count(productid) from product)

B*g2011-06-22 07:06

6 楼

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*l2011-06-22 07:06

7 楼

要做一个
SELECT DISTINCT CUSTID, PORDUCTID FROM SALE
才能用上面的QUERY

a92011-06-22 07:06

8 楼

到少要distinct一下吧，呵呵。

【在 c**i 的大作中提到】

: select custid
: from sales
: group by custid
: having count(productid) = (select count(productid) from product)

c*i2011-06-22 07:06

9 楼

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*w2011-06-22 07:06

10 楼

不用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)