一道难题 100分

[guanjm]

有张好友关系表 T(UseridA,UserIDB,RlationType)写出你认为最优的SQL实现如下功能：
某人的好友的好友但非自己的好友及关注对象列表，显示前20个，排序规则自定义
（RlationType：1为好友，2为关注，好友是双向冗余的，一个好友关系会有两条数据）

[叶子]

--设某人的UserID为A 设自己的UserID为B。
--得到B的好友
select * from T where UseridA='B' where RlationType=1

--得到自己的好友及关注对象
select * from T where UseridA='A'

--显示前20个，自定义排序
select top 20 * from tablename order by newid()

[叶子]

select top 20 a.* from 
(select * from T where UseridA='B' where RlationType=1) a
left join 
(select * from T where UseridA='A') b 
on a.userida=b.userida
where b.userida is null

[guanjm]

一个好友关系会有两条数据
你的好像不对。

[guanjm]

能不能用CONNECT BY来写下。

[叶子]

好友是双向冗余的?
如果A加了B，但是B还没有加A的时候，算不算是好友关系？

[guanjm]

[Quote=引用 5 楼 maco_wang 的回复:]
好友是双向冗余的?
如果A加了B，但是B还没有加A的时候，算不算是好友关系？
[/Quote]
不算

[-晴天]

select top 20 * from(
select * from t where useridA='我'
union all
select useridB,useridA,rlationtype from t where useridB='我'
)t
order by useridB

[oO寒枫Oo]

用断言

[-晴天]

;with cte as(
select useridB from(
select * from t where useridA='我'
union all
select useridB,useridA,rlationtype from t where useridB='我'
)t
)select top 20 * from t where useridA a where useridA!='我' and useridB!='我' and exists(select 1 from cte where useridB=a.useridA or useridA=a.useridA)
order by rlationtype

[-晴天]

;with cte as(
select useridB from(
select * from t where useridA='我'
union all
select useridB,useridA,rlationtype from t where useridB='我'
)t
)select top 20 * from t a where useridA!='我' and useridB!='我' and exists(select 1 from cte where useridB=a.useridA or useridA=a.useridA)
order by rlationtype

[cd731107]

--尝试写一下
--a和b代表两个好友，c和d也代表两个好友,且b.UseridA=c.UseridA，且指定a.UseridA=100代表某个表
select top 20 *
from t as a,t as b,t as c,t as d
where a.UseridA=b.UserIDB and a.UserIDB=b.UseridA and a.RlationType=1 and b.RlationType=1
and   a.UseridA=100
and   c.UseridA=d.UserIDB and c.UserIDB=d.UseridA and c.RlationType=1 and d.RlationType=1
and   b.UseridA=c.UseridA
and not exists 
(select 1 from t as tb where tb.UseridA=a.UseridA and tb.UserIDB=c.UserIDB and RlationType in (1,2))
and not exists 
(select 1 from t as tb where tb.UserIDB=a.UseridA and tb.UseridA=c.UserIDB and RlationType in (1,2))

[lhblxm]

--取得好友关系，假设某人的ID为@UserID
select useridb from t where userida=@userid where rlationType=1 where useridb in (
select userida from t where useridb=@userid where rlationType=1)

--好友的好友
with f as (select useridb from t where userida=@userid where rlationType=1 where useridb in (
select userida from t where useridb=@userid where rlationType=1)
,ff as (select useridb from t where userida in (select useridb from f) where rlationType=1 where useridb in (
select userida from t where useridb userida in (select useridb from f) where rlationType=1)
)
select top 20 from ff where useridb not in (select useridb from t where userid a=@userid)

[lhblxm]

--取得好友关系，假设某人的ID为@UserID
select t.useridB from t 
join t a on t.useridb=a.userida and t.userida=a.useridb 
where userida=@UserID


--好友的好友
select top 20 b.useridB from t 
join t a on t.useridb=a.userida and t.userida=a.useridb 
join t b on a.useridb=b.userida and a.userida=b.useridb 
where t.rlationType=1 and a.rlationType=1 and b.rlationType=1 and t.userida=@userid 
and b.useridb not in (select useridb from t where userida=@userida union all select @userid)