好友Freiend表结构如下:ID int ,
UserID int ,
FriendID int,
UserName varchar(20),
FriendName varchar(20)例如存在A、B、C三个用户。A与B为好友。C与B为好友。当前用户为A通过B找到C这个共同好友。
SQL怎么写,请各位高手指教。
UserID int ,
FriendID int,
UserName varchar(20),
FriendName varchar(20)例如存在A、B、C三个用户。A与B为好友。C与B为好友。当前用户为A通过B找到C这个共同好友。
SQL怎么写,请各位高手指教。
ID=
(select FriendID from Friend F1
join Friend F2
on F1.FriendID=F2.FriendID )
'跟这个有点类似'
/*
有如下信息:
起始地 目的地 距离(公里)
A B 1000
A C 1100
A D 900
A E 400
B D 300
D F 600
E A 400
F G 1000
C B 600
请用SQL语句或一段代码写出从A出发,可达到的目的地(包括间接方式)。
*/use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
[start] char(2),
[end] char(2),
[length] int
)
go
--插入测试数据
insert into tb select 'A','B',1000
union all select 'A','C',1100
union all select 'A','D',900
union all select 'A','E',400
union all select 'B','D',300
union all select 'D','F',600
union all select 'E','A',400
union all select 'F','G',1000
union all select 'C','B',600
go
--代码实现;with t as (
select *,cast([start]+','+[end] as varchar(8000)) result
from tb
where [start]='A'
union all
select a.*,cast(b.[result]+','+a.[end] as varchar(8000)) result
from tb a,t b
where a.[start]=b.[end] and b.[end]<>'A'
)
select [result] from t where [end]<>'A'
/*测试结果result
---------------------
A ,B
A ,C
A ,D
A ,E
A ,D ,F
A ,D ,F ,G
A ,C ,B
A ,C ,B ,D
A ,C ,B ,D ,F
A ,C ,B ,D ,F ,G
A ,B ,D
A ,B ,D ,F
A ,B ,D ,F ,G (13行受影响)
*/
1 1 2 A B
2 2 3 B C
3 3 4 C D
例如存在A、B、C三个用户。A与B为好友。C与B为好友。当前用户为A通过B找到C这个共同好友。
SQL怎么写,请各位高手指教。
CREATE TABLE [tb]
(
[ID] int NULL ,
[UserID] int NULL ,
[FriendID] int NULL ,
[UserName] varchar(100) NULL ,
[FriendName] varchar(100) NULL
)
GO--插入测试数据
INSERT INTO [tb] ([ID],[UserID],[FriendID],[UserName],[FriendName])
SELECT '1','1','2','A','B' UNION
SELECT '2','2','3','B','C' UNION
SELECT '3','3','4','C','D'
GO ----------------以下是楼主需要的代码----------------with cte as
(
select [UserID],[FriendID],[UserName],[FriendName] from tb where UserName='A' --
union all
select a.[UserID],a.[FriendID],a.[UserName],a.[FriendName] from tb a join cte b on a.[UserID]=b.[FriendID]
)
select [FriendID],[FriendName] from cte
where [FriendID] NOT IN(select [FriendID] from tb where UserName='A')--A的相关好友
where m.UserName = 'A' and n.UserName = 'C' and m.FriendName = n.FriendNameselect m.* , n.* from tb m, tb n
where m.UserName = 'A' and n.UserName = 'C' and m.FriendName = n.FriendName and m.FriendName = 'B'
ID UserID FriendID UserName FriendName
1 1 2 A B
ID UserID FriendID UserName FriendName
2 2 1 B A
ID UserID FriendID UserName FriendName
3 3 2 c B
ID UserID FriendID UserName FriendName
4 2 3 B C