求一条SQL语句问题

Question

有三张表

UserInfo用户信息表 字段 uid, uname

 Product产品表 字段 pid,pname

 Buyinfo购买信息表 bid,uid,pid

查询购买所有产品的用户该怎么实现？

Answer 1

select unamefrom dbo.UserInfowhere uid in ( select uid from (select count(1) as pcount,uid  from (select distinct uid,pid from Buyinfo) a group by uid) b inner join (select count(1) as pcount from Product) c on c.pcount = b.pcount) 

Answer 2

刚才理解错了..

Answer 3

是查询购买了所有产品的用户

Answer 4

是查询购买了所有产品的用户 那些用户购买了所有的产品 不是查所有用户的购买信息

Answer 5

@ Raycloud:是查询,一个客户买了那些产品,把每个产品列出来对吗?

Answer 6

提供个思路:   1) 将购买信息表根据Uid进行分组，同时去除重复购买信息   2) 将上述结果Count下和产品表的Count对比，相等则说明用户购买了所有商品   3) 最后将用户信息表的uid和第二步结果的uid对比select u.uid from UserInfo as u where u.uid = (select uid from  buyinfo as b where uid=(select top 1 uid from buyinfo where uid=b.uid) group by uid having Count(uid)= (select Count(pid) from product))

Answer 7

思路是这样的但是你的语句有问题最后综合了select uname from UserInfo u join(select uid from (select uid,pid from Buyinfo group by uid,pid)a group by uid having count(pid)=(select count(1)from Product))tuon u.uid=tu.uid

Answer 8

看看我这个   SELECT u.uid '用户id',u.uname '用户' FROM userinfo uINNER JOIN    (SELECT uid  FROM buyinfo    GROUP BY  uid    HAVING COUNT(DISTINCT pid)=(SELECT COUNT(pid)from product)) AS fON u.uid=f.uid

Answer 9

呵呵，楼上的可以，学习了