表a
id,userid,name,age,sex
1 1 1 2 3
2 1 4 5 6
3 2 1 1 1
表b
id userid,friendid,
1 1 1
2 1 1
3 2 2
b.friendid=a.id
我想通过userid=1查询表b的全部属于userid=1的friendid,和friendid所对应的a表的name,age,sex
我想实现的是:
friendid name age sex
1 1 2 3
1 4 5 6急用,谢谢
id,userid,name,age,sex
1 1 1 2 3
2 1 4 5 6
3 2 1 1 1
表b
id userid,friendid,
1 1 1
2 1 1
3 2 2
b.friendid=a.id
我想通过userid=1查询表b的全部属于userid=1的friendid,和friendid所对应的a表的name,age,sex
我想实现的是:
friendid name age sex
1 1 2 3
1 4 5 6急用,谢谢
from a,b
where b.userid=1
and a.id=b.friendid
-- Author:Flystone
-- Version:V1.001
-- Date:2008-09-09 13:52:37
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id int,userid int,name int,age int,sex int)
Go
Insert into ta
select 1,1,1,2,3 union all
select 2,1,4,5,6 union all
select 3,2,1,1,1
Go
-- Test Data: tb
If object_id('tb') is not null
Drop table tb
Go
Create table tb(id int,userid int,friendid int)
Go
Insert into tb
select 1,1,1 union all
select 2,1,1 union all
select 3,2,2
Go
--Start
select distinct b.friendid ,a.name,a.age,a.sex
from ta a
right join tb b on b.friendid = a.userid
where b.userid = 1
--Result:
/*friendid name age sex
----------- ----------- ----------- -----------
1 1 2 3
1 4 5 6(所影响的行数为 2 行)*/
--End
insert @ta select 1, 1, 1, 2, 3
insert @ta select 2, 1, 4, 5, 6
insert @ta select 3, 2, 1, 1, 1
declare @tb table(id int, userid int,friendid int)
insert @tb select 1, 1, 1
insert @tb select 2, 1, 1
insert @tb select 3, 2, 2
select distinct b.friendid, a.name, a.age, a.sex from @ta a inner join @tb b on b.friendid=a.userid and b.userid=1friendid name age sex
----------- ----------- ----------- -----------
1 1 2 3
1 4 5 6
select friend.friendid,usertable.name,usertable.age,usertable.sex
from usertable,friend
where friend.friendid = usertable.userid
in (select friend.friendid from friend where friend.userid = 1);