WITH tmpTb
AS ( SELECT FID,
FParentID,
FIsUse
FROM t1
WHERE FIsUse = '1'
UNION
SELECT FID,
FParentID,
FIsUse
FROM t1
WHERE FID IN ( SELECT FParentID
FROM tmpTb )
)
SELECT *
FROM tmpTb
AS ( SELECT FID,
FParentID,
FIsUse
FROM t1
WHERE FIsUse = '1'
UNION
SELECT FID,
FParentID,
FIsUse
FROM t1
WHERE FID IN ( SELECT FParentID
FROM tmpTb )
)
SELECT *
FROM tmpTb
解决方案 »
- select CONVERT ( nvarchar(9),9,00)
- sqlserver2000维护计划创建的备份不能自动delete旧的backup
- 如果批量删除记录??急
- 重复的数据如何只取时间最大的?
- 导库问题:mysql-->sql server
- 用SQL语句导出表到CSV文件
- 我有一个字符串形式的二进制常量('1100011'),如何将它写入varbinary(8000)的字段中?
- 一个SQL 语句问题 请高手帮忙
- SqlServer2005中CLR User-Defined Types的存储限制问题!!!火大啊!!!!!!!
- 为什么使用表间关联时出错?(急,谢谢)
- htl258进来!~关于sql中IP地址转化成数值的问题!!!
- 字段值的提取
as
(
select * from t1 where fisuse=1
union all
select * from t1 where fparentid is null
union all
select a.* from t1 a,wsp b where b.fparentid=a.fid
)select distinct * from wsp order by fid
/*
标题:查询指定节点及其所有父节点的函数
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-05-12
地点:广东深圳
*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有父节点的函数
create function f_pid(@id varchar(3)) returns @t_level table(id varchar(3))
as
begin
insert into @t_level select @id
select @id = pid from tb where id = @id and pid is not null
while @@ROWCOUNT > 0
begin
insert into @t_level select @id select @id = pid from tb where id = @id and pid is not null
end
return
end
go--调用函数查询002(广州市)及其所有父节点
select a.* from tb a , f_pid('002') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
002 001 广州市(所影响的行数为 2 行)
*/--调用函数查询003(深圳市)及其所有父节点
select a.* from tb a , f_pid('003') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
003 001 深圳市(所影响的行数为 2 行)
*/--调用函数查询008(西乡镇)及其所有父节点
select a.* from tb a , f_pid('008') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
003 001 深圳市
007 003 宝安区
008 007 西乡镇(所影响的行数为 4 行)
*/drop table tb
drop function f_pid
if object_id('[tb]') is not null drop table [tb]
create table tb(FID nvarchar(10),FParentID nvarchar(10),FIsUse bit)insert [tb]
select '0', null, 0 union all
select '1', null, 0 union all
select '00', '0', 0 union all
select '01', '00', 1 union all
select '011', '01', 0 union all
select '012', '01', 1 union all
select '02', '00', 0 union all
select '021', '02', 1
Create function Getpids(@RawID nvarchar(MAX))--@RawID格式:01,012,021,
returns @IDS table(id nvarchar(10))
as
begin
declare @index int
declare @FID varchar(MAX)
declare @FIDS varchar(MAX)
set @FIDS=@RawID
set @index=CHARINDEX(',',@FIDS) while(@index>0)
begin
select @FID=substring(@FIDS,0,@index)
select @FIDS=right(@FIDS,len(@FIDS)-@index)
set @index=CHARINDEX(',',@FIDS)
while(@FID is not null)
begin
insert into @IDS(id) values(@FID)
set @FID=(select FParentID from tb where FID=@FID)
end
end
return
end
declare @SIDS varchar(Max)
select @SIDS=isnull(@SIDS,'')+FID+',' from tb where FIsUse=1select x.*
from tb x,(select distinct id from dbo.Getpids(@SIDS)) y
where x.fid=y.iddrop table tb
0 NULL 0
00 0 0
01 00 1
012 01 1
02 00 0
021 02 1