通过递归查询来实现:;with t(XuHao, ManName, ManPrior) as ( select 1,'马雷',null union all select 2,'刘三','胡涛' union all select 3,'郑平','马雷' union all select 4,'胡涛','郑平' union all select 5,'陈雨','刘三' ),tt as ( select manname,manprior,1 as level from t where manprior is nullunion allselect t.ManName,t.ManPrior,level + 1 from tt inner join t on tt.ManName = t.ManPrior )select manname from tt /* manname 马雷 郑平 胡涛 刘三 陈雨 */
if object_id('Tempdb..#t') is not null drop table #t create table #t( xuhao int identity(1,1) not null, ManName nvarchar(10) null, ManPrior nvarchar(10) null )Insert Into #t select '马雷',null union all select '刘三','胡涛' union all select '郑平','马雷' union all select '胡涛','郑平' union all select '陈雨','刘三';with cte as( select a.*,isnull(b.xuhao,0) as pid from #t a left join #t b on a.ManPrior=b.ManName ) select xuhao,ManName,ManPrior from cte order by pid -------------- xuhao ManName ManPrior ----------- ---------- ---------- 1 马雷 NULL 3 郑平 马雷 5 陈雨 刘三 4 胡涛 郑平 2 刘三 胡涛(5 行受影响)
如果只要名字,就这样;with cte as( select a.*,isnull(b.xuhao,0) as pid from #t a left join #t b on a.ManPrior=b.ManName ) select ManName from cte order by pid ----------------- 马雷 郑平 陈雨 胡涛 刘三(5 行受影响)
用这个试试: --drop table tbcreate table tb(XuHao int, ManName nvarchar(20), ManPrior nvarchar(20))insert into tb select 1,'马雷',null union all select 2,'刘三','胡涛' union all select 3,'郑平','马雷' union all select 4,'胡涛','郑平' union all select 5,'陈雨','刘三' select a.ManName from tb a left join tb b on a.ManPrior=b.ManName order by isnull(b.xuhao,0) /* ManName 马雷 郑平 陈雨 胡涛 刘三 */
--sql2000 版本 --创建测试数据 if object_id('tb') is not null drop table tb go create table tb(XuHao int, ManName nvarchar(20), ManPrior nvarchar(20))
insert into tb select 1,'马雷',null union all select 2,'刘三','胡涛' union all select 3,'郑平','马雷' union all select 4,'胡涛','郑平' union all select 5,'陈雨','刘三' go --查询指定节点及其所有子节点的函数 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[f_Cid]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[f_Cid] go CREATE FUNCTION f_Cid(@ManName varchar(20)) RETURNS @t_Level TABLE(ManName varchar(20),Level int) AS BEGIN DECLARE @Level int SET @Level=1 INSERT @t_Level SELECT @ManName,@Level WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ManName,@Level FROM tb a,@t_Level b WHERE a.ManPrior=b.ManName AND b.Level=@Level-1 END RETURN END GO select * from f_Cid('马雷')/* manname level 马雷 1 郑平 2 胡涛 3 刘三 4 陈雨 5 */
as
(
select 1,'马雷',null
union all select 2,'刘三','胡涛'
union all select 3,'郑平','马雷'
union all select 4,'胡涛','郑平'
union all select 5,'陈雨','刘三'
),tt
as
(
select manname,manprior,1 as level from t
where manprior is nullunion allselect t.ManName,t.ManPrior,level + 1
from tt
inner join t
on tt.ManName = t.ManPrior
)select manname
from tt
/*
manname
马雷
郑平
胡涛
刘三
陈雨
*/
if object_id('Tempdb..#t') is not null drop table #t
create table #t(
xuhao int identity(1,1) not null,
ManName nvarchar(10) null,
ManPrior nvarchar(10) null
)Insert Into #t
select '马雷',null union all
select '刘三','胡涛' union all
select '郑平','马雷' union all
select '胡涛','郑平' union all
select '陈雨','刘三';with cte as(
select a.*,isnull(b.xuhao,0) as pid from #t a left join #t b on a.ManPrior=b.ManName
)
select xuhao,ManName,ManPrior from cte order by pid
--------------
xuhao ManName ManPrior
----------- ---------- ----------
1 马雷 NULL
3 郑平 马雷
5 陈雨 刘三
4 胡涛 郑平
2 刘三 胡涛(5 行受影响)
select a.*,isnull(b.xuhao,0) as pid from #t a left join #t b on a.ManPrior=b.ManName
)
select ManName from cte order by pid
-----------------
马雷
郑平
陈雨
胡涛
刘三(5 行受影响)
用这个试试:
--drop table tbcreate table tb(XuHao int, ManName nvarchar(20), ManPrior nvarchar(20))insert into tb
select 1,'马雷',null
union all select 2,'刘三','胡涛'
union all select 3,'郑平','马雷'
union all select 4,'胡涛','郑平'
union all select 5,'陈雨','刘三'
select a.ManName
from tb a
left join tb b
on a.ManPrior=b.ManName
order by isnull(b.xuhao,0)
/*
ManName
马雷
郑平
陈雨
胡涛
刘三
*/
楼上得到的最后顺序:
/*
ManName
马雷
郑平
陈雨
胡涛
刘三
*/明显是错误的,
正确的是:马雷(第1个),郑平(第2个),胡涛(第3个),刘三(第4个),陈雨(第5个),.....
--sql2000 版本
--创建测试数据
if object_id('tb') is not null drop table tb
go
create table tb(XuHao int, ManName nvarchar(20), ManPrior nvarchar(20))
insert into tb
select 1,'马雷',null
union all select 2,'刘三','胡涛'
union all select 3,'郑平','马雷'
union all select 4,'胡涛','郑平'
union all select 5,'陈雨','刘三'
go
--查询指定节点及其所有子节点的函数
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[f_Cid]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[f_Cid]
go
CREATE FUNCTION f_Cid(@ManName varchar(20))
RETURNS @t_Level TABLE(ManName varchar(20),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ManName,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ManName,@Level
FROM tb a,@t_Level b
WHERE a.ManPrior=b.ManName
AND b.Level=@Level-1
END
RETURN
END
GO select * from f_Cid('马雷')/*
manname level
马雷 1
郑平 2
胡涛 3
刘三 4
陈雨 5
*/
这个问题我没有继续探索了。
@acefr,你使用存储过程,应该是可以的,但这不是我想要的,还是谢谢你。
参与的都有份!