http://expert.csdn.net/Expert/topic/1375/1375432.xml?temp=.6925318
--------
select *,dbo.fn_32gettopclass(Tc_id,default,2,default) from treeclass 层次用,分隔。
--------
select *,dbo.fn_32gettopclass(Tc_id,default,2,default) from treeclass 层次用,分隔。
解决方案 »
- 需要一个触发器来记录某个字段的更改情况!
- 数据库表的设计
- 数据库恢复的问题
- 难题讨论:对于毫秒级的分页要求你如何应对?
- sql2005的语句中有没有将图片放入数据库的语句
- 有一个varchar字段,里面记录着类似034,41,245的数字信息,如何按照数字的大小排序呢?(034<41<245)
- 企业中的应有千奇百怪(一个比较烦的SQL)???
- 急!MS server2000如何知道表是被哪个用户(IP、MAC地址等)创建的
- 主从表问题
- INSTEAD OF INSERT 触发器有什么用啊 急........
- 关于触发器的触发动作之前的语法
- 导入数据时提示:服务器:消息:8152,级别 16 将截断字符串或二进制数据,语句已中断.
请问,如何在sql server中,让id 自动增量啊!
tbl_dep
dep_id dep我想让填dep的时候,dep_id 能自己赋值!
怎么做啊,帮个忙啊!
create table tbl_dep (dep_id int identity(1,1),dep varchar(100))
go知道了就不要回复了!
我认为在SQL力所能及的情况下,下面的问题相对更合理一些:问题: 查询所有叶子节点,及其临近的 4 代祖先?答案:应该不难看出下面SQL语句的规律,
你可根据"指定"的层次另外编程序(循环或递归)构造这个动态SQL,希望对你有一些启发:select
id
,parentid
,(select parentid
from tree
where id = t.parentid
)
,(select parentid
from tree
where id = (select parentid
from tree
where id = t.parentid
)
)
,(select parentid
from tree
where id = (select parentid
from tree
where id = (select parentid
from tree
where id = t.parentid
)
)
)
,(select parentid
from tree
where id = (select parentid
from tree
where id = (select parentid
from tree
where id = (select parentid
from tree
where id = t.parentid
)
)
)
)
from tree T
where id not in (select parentid
from tree
)
select a.id as level1,b.id as level2,b.others
from table a,table b
where a.id=b.parentid
select a.id as level1,b.id as level2,c.id as level3,c.others
from table a,table b,table c
where a.id=b.parentid and b.id=c.parentid
@x integer
as
-- 调用测试: AppSP_GetAllAncestors 40
declare @sql0 varchar(8000)
set @sql0='(select parentid from tree where id = t.parentid)'
declare @Temp varchar(8000)
set @Temp=@sql0
declare @i int
set @i=@x
declare @j int
set @j=0
DECLARE @SQL varchar(8000)
SET @SQL=''while exists (select 1 from tree where id=(select parentid from tree where id= @i) )
select
@Temp =@SQL0
,@sql0 ='(select parentid from tree where id = ' + @sql0 + ') '
,@SQL = @SQL + ' as ''' + cast(@j+1 as varchar) + ''',' + @Temp
,@i=parentid,@j=@j+1
from Tree
where id=@iset @sql='select id as ''0'',parentid ' + @sql + ' as ''' + cast(@j+1 as varchar) + ''' from tree T where id =' + cast(@x as varchar)
exec(@sql)
alter proc AppSP_GetAllAncestors
@x integer
as
-- 调用测试: AppSP_GetAllAncestors 478
declare @j int
set @j=0declare @sql varchar(8000)
set @sql=''declare @i integer
select @i=parentid
from tree
where id=@xwhile exists (select 1 from tree where id=@i)
select @sql = @sql + ',(select ' + cast(parentid as varchar) + ') as ''' + cast(@j+2 as varchar) + ''''
,@i=parentid ,@j=@j+1
from Tree
where id=@i
set @sql='select id as ''0'',parentid as ''1''' + @sql + ' from tree T where id =' + cast(@x as varchar)
exec(@sql)
@x integer
as
-- 调用测试: AppSP_GetAllAncestors 20
declare @j int
set @j=0declare @sql varchar(8000)
set @sql=''declare @i integer
select @i=parentid
from tree
where id=@xwhile exists (select 1 from tree where id=@i)
select @sql = @sql + ',' + cast(parentid as varchar) + ' as ''' + cast(@j+2 as varchar) + ''''
,@i=parentid ,@j=@j+1
from Tree
where id=@i
set @sql='select id as ''0'',parentid as ''1''' + @sql + ' from tree T where id =' + cast(@x as varchar)
exec(@sql)前面的 SP都只能算一条记录!下面的sp 可以返回所有节点的"指定层数"的祖先:
alter proc AppSP_GetAllAncestors
@x integer
as
-- 调用测试: AppSP_GetAllAncestors 10
declare @sql0 varchar(8000)
set @sql0='(select parentid from tree where id = t.parentid)'
declare @Temp varchar(8000)
set @Temp=@sql0declare @i int
set @i=0
DECLARE @SQL varchar(8000)
SET @SQL=''while @i < @x
select
@Temp =@SQL0
,@sql0 ='(select parentid from tree where id = ' + @sql0 + ') '
,@SQL = @SQL + ' as ''' + cast(@i+1 as varchar) + ''',' + @Temp
,@i=@i+1
set @sql='select id as ''0'',parentid ' + @sql + ' as ''' + cast(@i+1 as varchar) + ''' from tree T'
exec(@sql)
我刚才想到一个新的思路。在MS SQL Server2000里不是支持将一个数据集做为存储函数的返回值吗?如果以此建立一个递归的存储函数,将新检索出的数据与已有的数据做联接后生成新的返回值,不就可以递归生成完整的树状视图了吗?
可惜我的机器上现在没有SQL Server,不然写出来试试。