sql怎么实现树查询

发布网友发布时间：2022-04-12 13:06

共2个回答

懂视网时间：2022-04-12 17:27

CREATE TABLE UNTECK_ORGANIZATION (
ID BIGINT NOT NULL,
LEVEL_ INTEGER,
NAME VARCHAR(64),
AREACODE VARCHAR(8),
ORGSEQ VARCHAR(128),
PARENTID BIGINT,
THESORT INTEGER,
DESCN VARCHAR(256),
DEL_FLAG INTEGER,
CREATE_TIME TIMESTAMP,
CREATE_USER VARCHAR(64),
UPDATE_TIME TIMESTAMP,
UPDATE_USER VARCHAR(64),
CODE VARCHAR(64),
ADDRESS VARCHAR(255),
CELLPHONE VARCHAR(24),
ORGTYPE VARCHAR(20),
PRIMARY KEY (ID) );

with temptab(ID,NAME,CODE,PARENTID) as

(
select a.ID,a.NAME,a.CODE,a.PARENTID
from UNTECK_ORGANIZATION a
where a.ID = 3413585099
union all
select sub.ID,sub.NAME,sub.CODE,sub.PARENTID
from UNTECK_ORGANIZATION sub, temptab super
where sub.PARENTID = super.ID
)
select ID,NAME,CODE,PARENTID from temptab

热心网友时间：2022-04-12 14:35

表格式如下：

cid pid cname
1 0 董事长
2 1 CEO
3 2 销售经理
4 2 IT经理
5 2 运营经理
6 3 销售主管
7 4 IT主管
8 5 运营主管
9 6 业务员
10 7 程序员
11 8 运营员

create function get_detail(
@id int
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.cid,@l
from table_2 a,@re b
where a.pid=b.id and b.level=@l-1
end
return
end
go

--调用(查询所有的子)
select a.*,level=b.level from table_2 a,get_detail(2)b where a.cid=b.id