SQL SERVER实现“查找好友的好友”的常用方法（原创）

 

 

--查找好友的好友信息，
use textdb

GO
SET ANSI_NULLS ON ----新建用户表和FRIEND表
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[friend](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [userid] [int] NOT NULL,
 [fid] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[user](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [username] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [age] [int] NULL,
 CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

 

--方法一，嵌入查询
select id,username,age from
(
      select f.fid from (
      select fid from friend where userid=1100119
      ) as a1,friend as f where a1.fid=f.userid
) as a,[user] as b where b.id=a.fid

--方法二，临时表的方法
drop table #ftable
drop table #fftable
select fid into #ftable from friend where userid=1100119
select friend.fid into #fftable from #ftable,friend where #ftable.fid=friend.userid
select * from [user],#fftable where [user].id=#fftable.fid

--方法三，表变量法
declare @ftable table(fid int)
insert @ftable select fid from friend where userid=1100119
declare @fftable table(fid int)
insert @fftable select friend.fid from friend inner join @ftable as v on friend.userid=v.fid
select * from @fftable as i,[user] where [user].id=i.fid

转载于:https://www.cnblogs.com/rasion/archive/2010/03/27/1698285.html