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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

/***************************************** ---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