我错了,早应该在这里来咨询了。树形表:
id,parentid,text
里面的数据是无限级(实际用时肯定是也有限的了)要求输出以下格式:
[{
id: 1,
text: 'A leaf Node',
leaf: true
},{
id: 2,
text: 'A folder Node',
children: [{
id: 3,
text: 'A child Node',
leaf: true
}]
}]对于leaf,是根据id和parentid来自动生成的,如果不是叶,那就应该有children,否则leaf就为true了。注:用存储过程实现最佳。
先来20分,如果问题解决了,再加80分另开帖子表达谢意。
先谢谢大家!
id,parentid,text
里面的数据是无限级(实际用时肯定是也有限的了)要求输出以下格式:
[{
id: 1,
text: 'A leaf Node',
leaf: true
},{
id: 2,
text: 'A folder Node',
children: [{
id: 3,
text: 'A child Node',
leaf: true
}]
}]对于leaf,是根据id和parentid来自动生成的,如果不是叶,那就应该有children,否则leaf就为true了。注:用存储过程实现最佳。
先来20分,如果问题解决了,再加80分另开帖子表达谢意。
先谢谢大家!
解决方案 »
- 一个部门表查询所有部门的问题!
- 求一SQL
- 简单的一个查询问题
- Order by 能否排序某field里的内容,如select * from table order by fiedl1 like ‘%头%’, field1 like ‘尾’ ??请赐教!!(请看例子)
- 只更新日期里的月份的SQL语句如何写?
- SQL语句怎么计算相同值的行数,各位高手请教
- 关于sql server 2005 的增量备份
- 导入数据后,需要一列序号列,有没有简单快捷的方法?
- 根据当前年月查询满60周岁人员
- 1、sql语句中 group by x,y 和group by y,x的结果有区别吗?2、group by 子句为什么不能有聚集函数但是非聚集函数又可以?
- 老师帮我看一下这个查询语句该怎么写?
- sql mible 中的TOP关键词的问题!
id: 1,
text: 'A leaf Node',
leaf: true
},{
id: 2,
text: 'A folder Node',
children: [{
id: 3,
text: 'A child Node',
leaf: true
}]
}] ----------------
输出成这个样子?
returns varchar(5000)
as begin
declare @rtn varchar(5000) set @rtn = '['
declare @id int, @text varchar(32), @leaf int
declare cur_folder cursor for
select
id
,text
,leaf = case when exists (select 1 from thetreetable b where b.parentid=a.id) then 0 else 1 end
from thetreetable a
where parentid=@fid
open cur_folder
fetch next from cur_folder into @id, @text, @leaf
while @@fetch_status!=-1 begin
set @rtn = @rtn+'{id:'+cast(@id as varchar)+','+char(10)
set @rtn = @rtn+'text:'''+@text+''','+char(10)
set @rtn = case @leaf when 1 then 'leaf: true'
else @rtn+'children: '+dbo.fn_showfolder(@id) end
+char(10)
set @rtn = @rtn + '}'
fetch next from cur_folder into @id, @text, @leaf
end
close cur_folder
deallocate cur_folder
set @rtn=@rtn+']'
return replace(@rtn,'}{','},{')
end
go
这段代码没有进行测试。是理论上的。你试试看
set nocount on
go
create table thetreetable (id int, parentid int, text varchar(32))
go
insert into thetreetable values (1,0,'Comp. A')
insert into thetreetable values (2,0,'Comp. B')
insert into thetreetable values (3,1,'Dept. 1')
insert into thetreetable values (4,1,'Dept. 2')
insert into thetreetable values (5,3,'Team a')
insert into thetreetable values (6,2,'Project')
-- 1
-- 3
-- 5
-- 4
-- 2
-- 6
go
create function dbo.fn_shownode(@fid int)
returns varchar(5000)
as begin
declare @rtn varchar(5000) set @rtn = '['
declare @id int, @text varchar(32), @leaf int
declare cur_folder cursor for
select
id
,text
,leaf = case when exists (select 1 from thetreetable b where b.parentid=a.id) then 0 else 1 end
from thetreetable a
where parentid=@fid
open cur_folder
fetch next from cur_folder into @id, @text, @leaf
while @@fetch_status!=-1 begin
set @rtn = @rtn+'{'+char(10)
set @rtn = @rtn+'id:'+cast(@id as varchar)+','+char(10)
set @rtn = @rtn+'text:'''+@text+''','+char(10)
set @rtn = @rtn+case @leaf when 1 then 'leaf: true'
else 'children: '+dbo.fn_shownode(@id) end
+char(10)
set @rtn = @rtn + '}'
fetch next from cur_folder into @id, @text, @leaf
end
close cur_folder
deallocate cur_folder
set @rtn=@rtn+']'
return replace(@rtn,'}{','},{')
end
go
print dbo.fn_shownode(0)
go
drop table thetreetable
drop function dbo.fn_shownode
go
/*
[{
id:1,
text:'Comp. A',
children: [{
id:3,
text:'Dept. 1',
children: [{
id:5,
text:'Team a',
leaf: true
}]
},{
id:4,
text:'Dept. 2',
leaf: true
}]
},{
id:2,
text:'Comp. B',
children: [{
id:6,
text:'Project',
leaf: true
}]
}]
*/