--> 测试数据: [emp]
if object_id('[emp]') is not null drop table [emp]
create table [emp] (id int,name varchar(4),bossid int)
insert into [emp]
select 1,'张三',0 union all
select 2,'杨杨',0 union all
select 3,'孙中',0 union all
select 4,'王一',1 union all
select 5,'李三',2select *,isnull((select top 1 name from [emp] where id=a.bossid),'') as bossname from [emp] as a
/*
---------------------------------
1 张三 0
2 杨杨 0
3 孙中 0
4 王一 1 张三
5 李三 2 杨杨*/
if object_id('[emp]') is not null drop table [emp]
create table [emp] (id int,name varchar(4),bossid int)
insert into [emp]
select 1,'张三',0 union all
select 2,'杨杨',0 union all
select 3,'孙中',0 union all
select 4,'王一',1 union all
select 5,'李三',2select *,isnull((select top 1 name from [emp] where id=a.bossid),'') as bossname from [emp] as a
/*
---------------------------------
1 张三 0
2 杨杨 0
3 孙中 0
4 王一 1 张三
5 李三 2 杨杨*/
from emp a left join emp b on a.bossid = b.id
create view v_getuserinfo
as
select id,name,bossid,isnull((select top 1 name from [emp] where id=a.bossid),'') as bossname
from [emp] as a
If object_id('emp') is not null
Drop table emp
Go
Create table emp(id int,name varchar(12),bossid int)
Go
Insert into emp
select 1,'张三',0 union all
select 2,'杨杨',0 union all
select 3,'孙中',0 union all
select 4,'王一',1 union all
select 5,'李三',2
Go
--Start
select a.id,a.name,a.bossid ,isnull(b.name,'') as bossname
from emp a left join emp b on a.bossid = b.id
--Result:
/*id name bossid bossname
----------- ------------ ----------- ------------
1 张三 0
2 杨杨 0
3 孙中 0
4 王一 1 张三
5 李三 2 杨杨(所影响的行数为 5 行)*/
--End