求一条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))tu
on u.uid=tu.uid

Answer 8

看看我这个

  SELECT u.uid '用户id',u.uname '用户' FROM userinfo u
INNER JOIN
   (SELECT uid  FROM buyinfo
   GROUP BY  uid
   HAVING COUNT(DISTINCT pid)=(SELECT COUNT(pid)from product)) AS f
ON u.uid=f.uid

Answer 9

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