34,590
社区成员
发帖
与我相关
我的任务
分享
--加上cross join t4
select * from(
select t2.gs,t2.bm,'王五' as jl,'上级' as gx,t1.zjl as ry,t4.bh,t4.zb from t2 inner join t1 on t1.gs=t2.gs
cross join t4
where t2.bmjl='王五'
union all
select t2.gs,t2.bm,'王五' as jl,'同级'as gx,t2.bmjl as ry,t4.bh,t4.zb from t2 cross join t4 where bmjl <>'王五'
union all
select t2.gs,t2.bm,'王五' as jl,'下级' as gx,t3.ry,t4.bh,t4.zb from t2 inner join t3 on t2.bm=t3.bm cross join t4 where bmjl='王五' and t3.ry <>'王五'
)t
create table t1(gs varchar(10),zjl nvarchar(10))
insert into t1 select 'A','张三'
insert into t1 select 'B','李四'
create table t2(gs varchar(10),bm int,bmjl nvarchar(10))
insert into t2 select 'A',1,'王五'
insert into t2 select 'A',2,'马六'
create table t3(ry nvarchar(10),gs varchar(10),bm int)
insert into t3 select '阿一','A',1
insert into t3 select '阿二','A',2
insert into t3 select '王五','A',1
create table t4(bh int,zb nvarchar(10))
insert into t4 select 1,'asdf'
insert into t4 select 2,'ffasf'
go
select * from(
select t2.gs,t2.bm,'王五' as jl,'上级' as gx,t1.zjl as ry from t2 inner join t1 on t1.gs=t2.gs where t2.bmjl='王五'
union all
select t2.gs,t2.bm,'王五' as jl,'同级'as gx,t2.bmjl as ry from t2 where bmjl<>'王五'
union all
select t2.gs,t2.bm,'王五' as jl,'下级' as gx,t3.ry from t2 inner join t3 on t2.bm=t3.bm where bmjl='王五' and t3.ry<>'王五'
)t,t4
go
drop table t1,t2,t3,t4
/*
gs bm jl gx ry bh zb
---------- ----------- ---- ---- ---------- ----------- ----------
A 1 王五 上级 张三 1 asdf
A 1 王五 上级 张三 2 ffasf
A 2 王五 同级 马六 1 asdf
A 2 王五 同级 马六 2 ffasf
A 1 王五 下级 阿一 1 asdf
A 1 王五 下级 阿一 2 ffasf
(6 行受影响)
*/
create table t1(gs varchar(10),zjl nvarchar(10))
insert into t1 select 'A','张三'
insert into t1 select 'B','李四'
create table t2(gs varchar(10),bm int,bmjl nvarchar(10))
insert into t2 select 'A',1,'王五'
insert into t2 select 'A',2,'马六'
create table t3(ry nvarchar(10),gs varchar(10),bm int)
insert into t3 select '阿一','A',1
insert into t3 select '阿二','A',2
insert into t3 select '王五','A',1
create table t4(bh int,zb nvarchar(10))
insert into t4 select 1,'asdf'
insert into t4 select 2,'ffasf'
go
select t2.gs,t2.bm,'王五' as jl,'上级' as gx,t1.zjl as ry from t2 inner join t1 on t1.gs=t2.gs where t2.bmjl='王五'
union all
select t2.gs,t2.bm,'王五' as jl,'同级'as gx,t2.bmjl as ry from t2 where bmjl<>'王五'
union all
select t2.gs,t2.bm,'王五' as jl,'下级' as gx,t3.ry from t2 inner join t3 on t2.bm=t3.bm where bmjl='王五' and t3.ry<>'王五'
go
drop table t1,t2,t3,t4
/*
gs bm jl gx ry
---------- ----------- ---- ---- ----------
A 1 王五 上级 张三
A 2 王五 同级 马六
A 1 王五 下级 阿一
(3 行受影响)
*/
create table t1(gs varchar(10),zjl nvarchar(10))
insert into t1 select 'A','张三'
insert into t1 select 'B','李四'
create table t2(gs varchar(10),bm int,bmjl nvarchar(10))
insert into t2 select 'A',1,'王五'
insert into t2 select 'A',2,'马六'
create table t3(ry nvarchar(10),gs varchar(10),bm int)
insert into t3 select '阿一','A',1
insert into t3 select '阿二','A',2
insert into t3 select '王五','A',1
create table t4(bh int,zb nvarchar(10))
insert into t4 select 1,'asdf'
insert into t4 select 2,'ffasf'
go
select t2.gs,t2.bm,t1.zjl as ry,'上级' as gx from t2 inner join t1 on t1.gs=t2.gs where t2.bmjl='王五'
union all
select t2.gs,t2.bm,t2.bmjl as ry,'同级'as gx from t2
union all
select t2.gs,t2.bm,t3.ry,'下级' as gx from t2 inner join t3 on t2.bm=t3.bm where bmjl='王五' and t3.ry<>'王五'
go
drop table t1,t2,t3,t4
/*
gs bm ry gx
---------- ----------- ---------- ----
A 1 张三 上级
A 1 王五 同级
A 2 马六 同级
A 1 阿一 下级
(5 行受影响)
*/
create table t1(gs varchar(10),zjl nvarchar(10))
insert into t1 select 'A','张三'
insert into t1 select 'B','李四'
create table t2(gs varchar(10),bm int,bmjl nvarchar(10))
insert into t2 select 'A',1,'王五'
insert into t2 select 'A',2,'马六'
create table t3(ry nvarchar(10),gs varchar(10),bm int)
insert into t3 select '阿一','A',1
insert into t3 select '阿二','A',2
insert into t3 select '王五','A',1
create table t4(bh int,zb nvarchar(10))
insert into t4 select 1,'asdf'
insert into t4 select 2,'ffasf'
go
select t2.gs,t2.bm,t1.zjl as ry,'上级' as gx from t2 inner join t1 on t1.gs=t2.gs where t2.bmjl='王五'
union all
select t2.gs,t2.bm,t2.bmjl as ry,'同级'as gx from t2
union all
select t2.gs,t2.bm,t3.ry,'下级' as gx from t2 inner join t3 on t2.gs=t3.gs where bmjl='王五' and t3.ry<>'王五'
go
drop table t1,t2,t3,t4
/*
gs bm ry gx
---------- ----------- ---------- ----
A 1 张三 上级
A 1 王五 同级
A 2 马六 同级
A 1 阿一 下级
A 1 阿二 下级
(5 行受影响)
*/