SQL 一个表中的两个外键来自于同一个表创建的视图

/***************************************** ---Geovin Du 塗聚文 締友計算機信息技術有限公司 ---公司集團有親屬員工顯示介紹人的姓名。一個表中的兩個外鍵是來自於同一個表 *****************************************/ --員工表 IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[Employeetest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [Employeetest] GO create table Employeetest ( Eid INT IDENTITY(1,1) PRIMARY KEY, Ename nvarchar(30) not null, ) --插入數據 insert into Employeetest(Ename) values('geovindu') insert into Employeetest(Ename) values('geovindu1') insert into Employeetest(Ename) values('geovindu2') insert into Employeetest(Ename) values('geovindu3') insert into Employeetest(Ename) values('geovindu4') insert into Employeetest(Ename) values('geovindu5') ---公司內部介紹人表 IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[Retationtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [Retationtest] GO create table Retationtest ( rid INT IDENTITY(1,1) PRIMARY KEY, REkey int not null, --員工(介紹人)外键 REpkey int not null,--員工(員工)外键 ReationsName nvarchar(30) not null ) --插入 insert into Retationtest(REkey,REpkey,ReationsName) values(1,3,'sz') --函數 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetName]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[GetName] CREATE function GetName ( @REpkey int --参数 ) returns varchar(500) as begin declare @ename nvarchar(50) select @ename=Ename from Employeetest where Eid=@REpkey return @ename end GO --測試 declare @ename nvarchar(50),@REpkey int set @REpkey=2 select @ename=Ename from Employeetest where Eid=@REpkey select @ename --視圖 IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'View_Employeetest') DROP VIEW View_Employeetest GO create view View_Employeetest as select rid AS 'ID',Ename AS '介紹人姓名',dbo.GetName(REpkey) AS '員工姓名', ReationsName AS '關系' from Employeetest,Retationtest where Employeetest.Eid=Retationtest.REkey --and Employeetest.Eid=Retationtest.REpkey GO --顯示 select * from View_Employeetest /***************************************** ---G