请大家帮忙看看,sql语句有关问题
请大家帮忙看看,sql语句问题
表:
编号 职级 推荐人
001 B 003
002 C 001
004 B 001
003 A zz
006 A zz
005 B 006
想得到以下表:
编号 职级 上级领导
001 B 003
004 B 003
005 B 006
002 C 001
……
同职级推荐的按照推荐人向上找,所得上级领导职级要高于本职级
例如编号004 要找到他的上级领导是003
请问大家从优化的角度应该怎么写sql语句呢?
------解决方案--------------------
create table test
(
a char(3),
b char(1),
c char(3)
)
INSERT INTO TEST (A,B,C) VALUES ( '001 ', 'B ', '003 ')
INSERT INTO TEST (A,B,C) VALUES ( '002 ', 'C ', '001 ')
INSERT INTO TEST (A,B,C) VALUES ( '004 ', 'B ', '001 ')
INSERT INTO TEST (A,B,C) VALUES ( '003 ', 'A ', 'zz ')
INSERT INTO TEST (A,B,C) VALUES ( '006 ', 'A ', 'zz ')
INSERT INTO TEST (A,B,C) VALUES ( '005 ', 'B ', '006 ')
select tbl1.a,tbl1.b,tbl2.a as c from test as tbl1
left join test as tbl2 on tbl1.c=tbl2.a and tbl1.b > tbl2.b
where tbl2.a is not null
union all
select tbl3.a,tbl3.b,tbl4.a as c from (
select tbl1.a,tbl1.b,tbl2.c as c from test as tbl1
left join test as tbl2 on tbl1.c=tbl2.a and tbl1.b = tbl2.b
where tbl2.a is not null
)tbl3
left join test as tbl4 on tbl3.c=tbl4.a and tbl3.b > tbl4.b
A B C
004 B 003
002 C 001
001 B 003
005 B 006
表:
编号 职级 推荐人
001 B 003
002 C 001
004 B 001
003 A zz
006 A zz
005 B 006
想得到以下表:
编号 职级 上级领导
001 B 003
004 B 003
005 B 006
002 C 001
……
同职级推荐的按照推荐人向上找,所得上级领导职级要高于本职级
例如编号004 要找到他的上级领导是003
请问大家从优化的角度应该怎么写sql语句呢?
------解决方案--------------------
create table test
(
a char(3),
b char(1),
c char(3)
)
INSERT INTO TEST (A,B,C) VALUES ( '001 ', 'B ', '003 ')
INSERT INTO TEST (A,B,C) VALUES ( '002 ', 'C ', '001 ')
INSERT INTO TEST (A,B,C) VALUES ( '004 ', 'B ', '001 ')
INSERT INTO TEST (A,B,C) VALUES ( '003 ', 'A ', 'zz ')
INSERT INTO TEST (A,B,C) VALUES ( '006 ', 'A ', 'zz ')
INSERT INTO TEST (A,B,C) VALUES ( '005 ', 'B ', '006 ')
select tbl1.a,tbl1.b,tbl2.a as c from test as tbl1
left join test as tbl2 on tbl1.c=tbl2.a and tbl1.b > tbl2.b
where tbl2.a is not null
union all
select tbl3.a,tbl3.b,tbl4.a as c from (
select tbl1.a,tbl1.b,tbl2.c as c from test as tbl1
left join test as tbl2 on tbl1.c=tbl2.a and tbl1.b = tbl2.b
where tbl2.a is not null
)tbl3
left join test as tbl4 on tbl3.c=tbl4.a and tbl3.b > tbl4.b
A B C
004 B 003
002 C 001
001 B 003
005 B 006