好友关系：查询共同的好友的查询

好友关系表，记录着每个人的好友情况，查与uid为6共同好友的情况

 

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[uid] int,[frienduid] int)
insert [tb]
select 1,1,2 union all
select 2,1,3 union all
select 3,5,2 union all
select 4,5,3 union all
select 5,6,3 union all
select 6,6,2 union all
select 7,6,4 union all
select 8,1,4 union all
select 9,7,2 union all
select 10,7,3 union all
select 11,7,4 union all
select 12,7,1 

--以下语句为求共同朋友的个数，在求表数据的基础上进行下汇总就可以了
select uid, count(*) as NumSameFriend--[frienduid]--
from tb a
where exists(select * from tb where uid=6 and frienduid=a.frienduid and uid<>a.uid)
group by a.uid

--求包括uid=6的所有朋友，还有其他朋友的Uid
select a.uid
from tb a
left join tb b on (b.uid=6 and a.frienduid=b.frienduid)
 where a.uid<>6
 group by a.uid
 having COUNT(a.uid)>COUNT(b.uid)
 
 select a.uid
from tb a
left join tb b on (b.uid=6 and a.frienduid=b.frienduid)
 where a.uid<>6
 group by a.uid
 having COUNT(a.uid)=(select COUNT(1) from tb where uid=6)