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

最新推荐文章于 2022-09-17 15:06:46 发布

geovi

最新推荐文章于 2022-09-17 15:06:46 发布

阅读量2.1k

点赞数

分类专栏：数据库编程文章标签： sql insert table object null function

本文链接：https://blog.csdn.net/geovi/article/details/6579138

版权

数据库编程专栏收录该内容

36 篇文章 0 订阅

订阅专栏

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