求一条SQL语句问题
0
悬赏园豆：80
[已解决问题]
解决于 2011-06-16 09:24
有三张表
UserInfo用户信息表 字段 uid, uname
Product产品表 字段 pid,pname
Buyinfo购买信息表 bid,uid,pid
查询购买所有产品的用户该怎么实现？
问题补充：
难道表达有问题？
是查询购买了所有产品的用户 或者说是 那些用户购买了所有的产品 不是查所有用户的购买信息
最佳答案
0
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)
收获园豆：70
能实现效果
--在用户表里找到用户姓名,子查询产品表,对关系表BUyinfo字段UID分组查询,记录个数,如果个数=product表的个数说明满足条件,--
select UserInfo.UName from UserInfo where UserInfo.UID in(select uid from BUyinfo group by uid having COUNT(*)=(select COUNT(*) from Product) )
select UserInfo.UName from UserInfo where UserInfo.UID in(select uid from BUyinfo group by uid having COUNT(*)=(select COUNT(*) from Product) )
其他回答(7)
0
刚才理解错了..
0
SELECT C.uname ,A.pname FROM Product A JOIN BuyInfo B ON A.pid = B.pid JOIN UserInfo C ON B.uid = C.uid
如果要过滤重复就加在C.uname前面加个DISTINCT
是查询购买了所有产品的用户
0
declare @num int
select @num=count(pid) from product
select u.uid,u.uname from buyinfo b,userinfo u where b.uid=u.uid and count(b.pid)=@num
0
select uname as 用户姓名,pname as 产品名称 from buyinfo join product on(buyinfo.pid=product.pid) join userInfo on(buyinfo.uid=userinfo.uid)
用外连接也可以,查询他的值不为null
是查询购买了所有产品的用户 那些用户购买了所有的产品 不是查所有用户的购买信息
@
Raycloud:是查询,一个客户买了那些产品,把每个产品列出来对吗?
Raycloud:是查询,一个客户买了那些产品,把每个产品列出来对吗?
0
提供个思路:
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))
收获园豆：10
思路是这样的
但是你的语句有问题
最后综合了
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
但是你的语句有问题
最后综合了
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
0
看看我这个
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
0
呵呵，楼上的可以，学习了
