--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@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.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@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.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
******************************************************************************* 除了名称还要把channelid也提取出来。*******************************************************************************麻烦各位大侠了!
channelid
channelname
pchannelid
depthchannelname表示频道名称
depth用来表示频道的级别,0表示父频道,1表示一级子频道,2表示二级子频道,……,N表示N级子频道。
pchannelid是上一级频道的channelid我要用一句sql语句把全部频道列出来,顺序如下:
父频道名称1
一级子频道名称1(父频道名称1下的)
二级子频道名称1(父频道名称1,一级子频道名称1下的)
二级子频道名称2(父频道名称1,一级子频道名称1下的)
一级子频道名称2(父频道名称1下的)
父频道名称2
一级子频道名称1(父频道名称2下的)
二级子频道名称1(父频道名称2,一级子频道名称1下的)
二级子频道名称2(父频道名称2,一级子频道名称1下的)
一级子频道名称2(父频道名称2下的)依次类推,将全部“频道名称”和“channelid”都调出来。如果一句sql不能完成的话,就用多个sql语句(我使用asp调用的,请把相关的代码写的比较详细点)谢谢啦
insert into channel select 1,'aaa',0,0
insert into channel select 2,'aaaa1',1,1
insert into channel select 3,'aaaa2',1,1
insert into channel select 4,'aaaa3',1,1
insert into channel select 5,'aaaa4',2,2 Create FUNCTION 子频道名称(
@ID int
)
RETURNS nvarchar(4000)
AS
BEGIN
declare @pp nvarchar(4000),@ii int
set @pp=''
if exists(select * from channel where channelid=@ID)
begin
select @pp=channelname,@ii=pchannelid from channel where channelid=@ID
set @pp=rtrim(@pp)+'->'+rtrim(dbo.子频道名称(@ii))
end
return @pp
endselect left(mc,len(mc)-3) from (
select rtrim(channelname)+'->'+rtrim(dbo.子频道名称(pchannelid)) as mc from channel) as c
order by mc asc
insert into channel select 1,'aaa',0,0
insert into channel select 2,'aaaa1',1,1
insert into channel select 3,'aaaa2',1,1
insert into channel select 4,'aaaa3',1,1
insert into channel select 5,'aaaa4',2,2 Create FUNCTION 子频道名称(
@ID int
)
RETURNS nvarchar(4000)
AS
BEGIN
declare @pp nvarchar(4000),@ii int
set @pp=''
if exists(select * from channel where channelid=@ID)
begin
select @pp=channelname,@ii=pchannelid from channel where channelid=@ID
set @pp=rtrim(@pp)+'->'+rtrim(dbo.子频道名称(@ii))
end
return @pp
endselect left(mc,len(mc)-2) from (
select rtrim(channelname)+'->'+rtrim(dbo.子频道名称(pchannelid)) as mc from channel) as c
order by mc asc
/*结果
aaa
aaaa1->aaa
aaaa2->aaa
aaaa3->aaa
aaaa4->aaaa1->aaa
*/
retruns varchar(1000)
as
begin
declare @s varchar(1000)
select @s = channelname,@id = rtrim(pchannelid) from channel where channelid = @id
while exists(select 1 from from channel where channelid = @id)
select @s = @s+channelname,@id = rtrim(pchannelid) from channel where channelid = @id
return @s
end
go
select * from channel order by dbo.getpath(rtrim(channelid))
retruns varchar(1000)
as
begin
declare @s varchar(1000)
while exists(select 1 from from channel where channelid = @id)
select @s = channelname+isnull(@s,''),@id = rtrim(pchannelid) from channel where channelid = @id
return @s
end
go
select * from channel order by dbo.getpath(rtrim(channelid))
returns @t table(channelid id,channelname varchar(50),pchannelid int,depth int,lev int)
as
begin
declare @lev int
set @lev=1
insert into @t select *,@lev from channel where channelid=0
WHILE @@ROWCOUNT>0
BEGIN
SET @lev=@lev+1
INSERT @t SELECT a.*,@lev from channel a,@t b
WHERE a.pchannelid=b.channelid AND b.lev=@lev-1
END
RETURN
end--调用函数:
select * from dbo.wsp()
--上面手误写错了returns @t table(channelid id,channelname varchar(50),pchannelid int,depth int,lev int)
--改为:
returns @t table(channelid int,channelname varchar(50),pchannelid int,depth int,lev int)
你就当表示定义一个int变量,变量名称是@lev好了
敢问是否大家都用得sql2000……
ASP+SQL语句的代码怎么写。
在关键字 'select' 附近有语法错误。
@@开头为系统变量,如@@ERROR.
001
001.001
001.002
002
002.001
002.001.001
存放类似的编码,查询时就按这个Path排序就完了。只是在更新插入这个表时多了些逻辑