ID parentid level level bomid
----------------------------------------------------
487 0 1 + 275
488 487 2 ++ 41
489 488 3 +++ 2098
490 489 4 ++++ 824
491 490 5 +++++ 825
492 491 6 ++++++ 784
493 487 2 ++ 41
494 487 2 ++ 41
495 487 2 ++ 41
496 487 2 ++ 41
497 496 3 +++ 2099
498 497 4 ++++ 823
499 498 5 +++++ 827
500 499 6 ++++++ 785
501 0 1 + 275
502 0 1 + 275根据level将ID填入parentid中,以上是已经填好的
我写了一个只能填一部分id 493-496无法算了update hzkf_xx set parentid=levelno-1
declare @id nvarchar(20)
declare @parentid nvarchar(20)
declare @levelno int
declare @cid int
declare @level1 int
declare @tlevel int
declare @tparentid int
DECLARE BOM CURSOR FOR
select ID,ParentID,levelno
from hzkf_xx
open BOM
FETCH NEXT FROM BOM
into @id,@parentid,@levelnoWHILE @@FETCH_STATUS = 0
BEGIN if (@levelno=1)
begin
set @tparentid=@parentid
set @tlevel=@id
set @cid=@id
set @level1=@levelno
--print @id
end
if (@levelno>1)
begin
if (@levelno>@level1)
begin
update hzkf_xx set parentid=@cid where id=@id
set @cid=@id
set @level1=@levelno
end end
if (@levelno<=@level1)
begin
set @cid=@id
set @level1=@levelno
end
FETCH NEXT FROM BOM
into @id,@parentid,@levelno
ENDCLOSE BOM
DEALLOCATE BOM
----------------------------------------------------
487 0 1 + 275
488 487 2 ++ 41
489 488 3 +++ 2098
490 489 4 ++++ 824
491 490 5 +++++ 825
492 491 6 ++++++ 784
493 487 2 ++ 41
494 487 2 ++ 41
495 487 2 ++ 41
496 487 2 ++ 41
497 496 3 +++ 2099
498 497 4 ++++ 823
499 498 5 +++++ 827
500 499 6 ++++++ 785
501 0 1 + 275
502 0 1 + 275根据level将ID填入parentid中,以上是已经填好的
我写了一个只能填一部分id 493-496无法算了update hzkf_xx set parentid=levelno-1
declare @id nvarchar(20)
declare @parentid nvarchar(20)
declare @levelno int
declare @cid int
declare @level1 int
declare @tlevel int
declare @tparentid int
DECLARE BOM CURSOR FOR
select ID,ParentID,levelno
from hzkf_xx
open BOM
FETCH NEXT FROM BOM
into @id,@parentid,@levelnoWHILE @@FETCH_STATUS = 0
BEGIN if (@levelno=1)
begin
set @tparentid=@parentid
set @tlevel=@id
set @cid=@id
set @level1=@levelno
--print @id
end
if (@levelno>1)
begin
if (@levelno>@level1)
begin
update hzkf_xx set parentid=@cid where id=@id
set @cid=@id
set @level1=@levelno
end end
if (@levelno<=@level1)
begin
set @cid=@id
set @level1=@levelno
end
FETCH NEXT FROM BOM
into @id,@parentid,@levelno
ENDCLOSE BOM
DEALLOCATE BOM
解决方案 »
- sql server调用jmail发邮件的问题
- SQL Server 2005 怎么玩?!
- 几百万数据的数据库查询
- 关于数据库保存时间的样式的问题 不需要保存日期,只需要保存时分秒
- 高分求救:用odbc连接时出现[Microsoft][odbc SQL Server ]错误
- 高手请进:多个板块使用同一用户如何设计用户表结构?
- 用backup log with no_log截断日志后收缩数据库,为了数据安全和完整,应作哪些工作?谢谢!
- 初来此版,大家能否推荐一下学习ADO的的经典书(如果有的话)。。。。?
- 关于记录调序?
- 学习了大力班主的《数据库设计规范》受益菲浅,对于里面提到的“对数据列,不可为空,以0作默认值”理解不是太深刻,想请高手们进来谈谈
- 求助:jsp报表的sql组合
- 触发器的问题(高手请进)
INSERT @TB
SELECT 487, 0, 1, '+', 275 UNION ALL
SELECT 488, 487, 2, '++', 41 UNION ALL
SELECT 489, 488, 3, '+++', 2098 UNION ALL
SELECT 490, 489, 4, '++++', 824 UNION ALL
SELECT 491, 490, 5, '+++++', 825 UNION ALL
SELECT 492, 491, 6, '++++++', 784 UNION ALL
SELECT 493, 487, 2, '++', 41 UNION ALL
SELECT 494, 487, 2, '++', 41 UNION ALL
SELECT 495, 487, 2, '++', 41 UNION ALL
SELECT 496, 487, 2, '++', 41 UNION ALL
SELECT 497, 496, 3, '+++', 2099 UNION ALL
SELECT 498, 497, 4, '++++', 823 UNION ALL
SELECT 499, 498, 5, '+++++', 827 UNION ALL
SELECT 500, 499, 6, '++++++', 785 UNION ALL
SELECT 501, 0, 1, '+', 275 UNION ALL
SELECT 502, 0, 1, '+', 275
SELECT ID,
CASE WHEN [parentid]=0 THEN 0 ELSE (SELECT TOP 1 ID FROM @TB WHERE ID<T.ID AND LEVEL=T.LEVEL-1 ORDER BY ID DESC) END AS PID,
LEVEL,
LEVEL2,
BOMID
FROM @TB AS T
/*
ID PID LEVEL LEVEL2 BOMID
----------- ----------- ----------- ------ -----------
487 0 1 + 275
488 487 2 ++ 41
489 488 3 +++ 2098
490 489 4 ++++ 824
491 490 5 +++++ 825
492 491 6 ++++++ 784
493 487 2 ++ 41
494 487 2 ++ 41
495 487 2 ++ 41
496 487 2 ++ 41
497 496 3 +++ 2099
498 497 4 ++++ 823
499 498 5 +++++ 827
500 499 6 ++++++ 785
501 0 1 + 275
502 0 1 + 275(16 行受影响)
*/
CASE WHEN LEVEL=1 THEN 0 ELSE (SELECT TOP 1 ID FROM @TB WHERE ID<T.ID AND LEVEL=T.LEVEL-1 ORDER BY ID DESC) END AS PID,
LEVEL,
LEVEL2,
BOMID
FROM @TB AS T
PID=ISNULL((SELECT TOP 1 ID FROM tb WHERE ID<T.ID AND LEVEL=t.LEVEL-1 ORDER BY ID DESC),0),
LEVEL,
LEVEL2,
BOMID
FROM tb t