问题

[shaqulle]

我有一个表的结构是这样的

base_id employee_id parentid position(职位)

如果有这样的数据

NS00000001 001 000 AAA
NS00000002 002 001 BBB
NS00000003 003 001 CCC
NS00000004 004 002 DDD

我在输入一个004的时候
希望返回：
NS00000001 001 000 AAA
NS00000002 002 001 BBB
NS00000004 004 002 DDD

求该SQL

[Limpire]

这个不行吗？

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id varchar(3),parentid varchar(3),position varchar(3))
insert into #T
select '001','000','AAA' union all
select '002','001','BBB' union all
select '003','001','CCC' union all
select '004','002','DDD'

;--2005
with T as
(
	select * from #T where id='004'
	union all
	select a.* from #T a join T b on b.parentid=a.id
)
select * from T order by 1

/*
001 000 AAA
002 001 BBB
004 002 DDD
*/

--2000
if object_id('tempdb.dbo.#') is not null drop table #
declare @level int
set @level=0
select *,level=@level into # from #T where id='004'
while @@rowcount>0
begin
	set @level=@level+1
	insert # select a.*,@level from #T a join # b on b.parentid=a.id and b.level=@level-1
end
select * from # order by 1

/*
id   parentid position level
---- -------- -------- -----------
001  000      AAA      2
002  001      BBB      1
004  002      DDD      0
*/

[liangCK]

8.2.4 查找指定节点的所有父节点的示例函数.sql

CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
	DECLARE @Level int
	SET @Level=1
	INSERT @t_Level SELECT @ID,@Level
	WHILE @@ROWCOUNT>0
	BEGIN
		SET @Level=@Level+1
		INSERT @t_Level SELECT a.PID,@Level
		FROM tb a,@t_Level b
		WHERE a.ID=b.ID
			AND b.Level=@Level-1
	END
	RETURN
END
GO


--上面的用户定义函数可以处理一个节点有多个父节点的情况，对于标准的树形数据而言，由于每个节点仅有一个父节点，所以也可以通过下面的用户定义函数实现查找标准树形数据的父节点。
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
	INSERT @t_Level SELECT @ID
	SELECT @ID=PID FROM tb
	WHERE ID=@ID
		AND PID IS NOT NULL
	WHILE @@ROWCOUNT>0
	BEGIN
		INSERT @t_Level SELECT @ID
		SELECT @ID=PID FROM tb
		WHERE ID=@ID
			AND PID IS NOT NULL
	END
	RETURN
END

[dawugui]

create table tb(base_id varchar(30) , id varchar(10) , parentid varchar(10) , position varchar(10))
insert into tb values('NS00000001' , '001' ,'000' ,'AAA') 
insert into tb values('NS00000002' , '002' ,'001' ,'BBB') 
insert into tb values('NS00000003' , '003' ,'001' ,'CCC') 
insert into tb values('NS00000004' , '004' ,'002' ,'DDD')
go

CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(id varchar(10) , Level int)
AS
BEGIN
    DECLARE @Level int
    SET @Level=1
    INSERT @t_Level SELECT @ID,@Level
    WHILE @@ROWCOUNT>0
    BEGIN
        SET @Level=@Level+1
        INSERT @t_Level SELECT a.parentid,@Level
        FROM tb a,@t_Level b
        WHERE a.id=b.id
            AND b.Level=@Level-1
    END
    RETURN
END
GO

SELECT a.*
FROM tb a,f_pid('004') b
WHERE a.id=b.id

drop table tb
drop function f_Pid

/*
base_id                        id         parentid   position   
------------------------------ ---------- ---------- ---------- 
NS00000001                     001        000        AAA
NS00000002                     002        001        BBB
NS00000004                     004        002        DDD

（所影响的行数为 3 行）
*/

[fcuandy]

啥意思?

??

select * from tb a where not exists(select * from tb where parentid=a.parentid and base_id<a.base_id) where employee_id<='004'

[fcuandy]

select * from tb a where not exists(select * from tb where parentid=a.parentid and base_id<a.base_id) and employee_id<='004'

[fcuandy]

原来是求父节点，看走眼了。

[Limpire]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (base_id varchar(10),employee_id varchar(3),parentid varchar(3),position varchar(3))
insert into #T
select 'NS00000001','001','000','AAA' union all
select 'NS00000002','002','001','BBB' union all
select 'NS00000003','003','001','CCC' union all
select 'NS00000004','004','002','DDD'

/*
*/

;--2005
with T as
(
	select * from #T where employee_id='004'
	union all
	select a.* from #T a join T b on b.parentid=a.employee_id
)
select * from T order by 1

/*
base_id    employee_id parentid position
---------- ----------- -------- --------
NS00000001 001         000      AAA
NS00000002 002         001      BBB
NS00000004 004         002      DDD
*/

--2000
if object_id('tempdb.dbo.#') is not null drop table #
declare @level int
set @level=0
select *,level=@level into # from #T where employee_id='004'
while @@rowcount>0
begin
	set @level=@level+1
	insert # select a.*,@level from #T a join # b on b.parentid=a.employee_id and b.level=@level-1
end
select * from # order by 1

/*
base_id    employee_id parentid position level
---------- ----------- -------- -------- -----------
NS00000001 001         000      AAA      2
NS00000002 002         001      BBB      1
NS00000004 004         002      DDD      0
*/