create view vi_porject
as
select a.项目编号,b.客户名称,c.职员姓名 as 跟单职员姓名,
d.职员姓名 as 审批职员姓名
from project a left join client b on a.客户编号=b.客户编号
left join employee c on a.跟单职员编号=c.职员编号
left join employee d on a.审批职员编号=d.职员编号
go
as
select a.项目编号,b.客户名称,c.职员姓名 as 跟单职员姓名,
d.职员姓名 as 审批职员姓名
from project a left join client b on a.客户编号=b.客户编号
left join employee c on a.跟单职员编号=c.职员编号
left join employee d on a.审批职员编号=d.职员编号
go
as
select
项目编号,
客户编号,
客户名称 = (select 客户名称 from client where 客户编号 = project.客户编号)
跟单职员编号,
跟单职员姓名 = (select 职员姓名 from client where 职员编号 = project.跟单职员编号)
审批职员编号,
审批职员姓名 = (select 职员姓名 from client where 职员编号 = project.审批职员编号)
from
project
go
As
Select
A.项目编号,
客户名称=B.客户名称,
跟单职员姓名=C.职员姓名,
审批职员姓名=D.职员姓名
from project A
Left Join client B
On A.客户编号=B.客户编号
Left Join employee C
On A.跟单职员编号=C.职员编号
Left Join employee D
On A.审批职员编号=D.职员编号
GO
Select * from List Order By A.项目编号
create table project (xmid int, khid varchar(4),
gdid varchar(4),spid varchar(4))
create table client (khid varchar(4),khname varchar(8))
create table employee (zyid varchar(4),zyname varchar(8))
---插入记录
insert project select 1,'001','gd01','sp01'
union all select 2,'002','gd01','sp02'
union all select 3,'001','gb02','sp01'
union all select 4,'003','gd02','sp02'
union all select 5,'001','gd03','sp03'
union all select 6,'002','gd01','sp02'insert client select '001','a1'
union all select '002','a2'
union all select '003','a3'
union all select '004','a4'insert employee select 'gd01','e1'
insert employee select 'gd02','e2'
insert employee select 'gd03','e3'
insert employee select 'gd04','e4'
insert employee select 'sp01','e5'
insert employee select 'sp02','e6'
insert employee select 'sp03','e7'
insert employee select 'sp04','e8'select * from project
select * from client
select * from employee
当显示全部的项目单记录时,要包括的内容有:
项目编号, 客户名称, 跟单职员姓名, 审批职员姓名Select p.xmid,c.khname,d.zyname,e.zyname from project p
Left join client c on p.khid=c.khid left join employee e
on p.gdid=e.zyid left join employee d on p.spid=d.zyid
是我弄错了,没有使用表别名除了libin_ftsafe(子陌红尘)外,其它的答案都对.谢谢大家!!