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]

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

--測試

declare @ename nvarchar(50),@REpkey int

set @REpkey=2

select @ename

--視圖

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

      WHERE TABLE_NAME = 'View_Employeetest')

   DROP VIEW View_Employeetest

create view View_Employeetest

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

--顯示

select * from View_Employeetest