id parent bm
1 -1
2 1
3 1
4 2
6 2
7 3
8 3
要得到
id parent bm
1 -1 001
2 1 001001
3 1 001002
4 2 001001001
6 2 001001002
7 3 001002001
8 3 001002002
parent對應id,根據id和parent 如何bm自動生產三位，不足前面補0
uj5u.com熱心網友回復：
沒人回復呀，怎么辦uj5u.com熱心網友回復：
要不要紅急解決，急uj5u.com熱心網友回復：
沒看出規律啊，parent 3 的 不應該是001001001001么?uj5u.com熱心網友回復：
用游標回圈賦值就可以了；create table tree_base(id int,parent int,bm varchar(200))
insert into tree_base(id,parent)
select 1,-1
union all
select 2,1
union all
select 3,1
union all
select 4,2
union all
select 6,2
union all
select 7,3
union all
select 8,3
declare @pid int;
declare @nid int;
declare @count int;
--首先根節點只有一個，置為001
update tree_base set bm= '00'+cast(1 as varchar) where parent not in (select id from parent)
--然后回圈取parent_id，并回圈賦值給子節點;
declare curson_name cursor for
select id, parent from tree_base where bm is null order by parent,id;
open curson_name
fetch next from curson_name into @nid,@pid
while @@FETCH_STATUS=0
begin
select @count=count(1)+1 from tree_base where parent=@pid and bm is not null
update tree_base
set bm=(select bm from tree_base where id=@pid )
+ right(('00'+ cast(@count as varchar(2))),3)
where id=@nid
--print @count
--print @nid
fetch next from curson_name into @nid,@pid
end
close curson_name
deallocate curson_name
--select * from tree_base
--update tree_base set bm=null
uj5u.com熱心網友回復：
create table #t(id int,parent int,bm varchar(200))
insert into #t(id,parent)
select 1,-1
union all
select 2,1
union all
select 3,1
union all
select 4,2
union all
select 6,2
union all
select 7,3
union all
select 8,3
WITH CTE_1
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY PARENT ORDER BY ID) AS SEQ FROM #T),
--SELECT * FROM CTE_1
CTE_2
AS
(SELECT *,CAST(RIGHT('00'+CAST(SEQ AS VARCHAR),3) AS VARCHAR) AS BM_NEW
FROM CTE_1 A
WHERE NOT EXISTS (SELECT 1 FROM CTE_1 WHERE ID=A.PARENT)
UNION ALL
SELECT A.*,CAST(B.BM_NEW+RIGHT('00'+CAST(A.SEQ AS VARCHAR),3) AS VARCHAR)
FROM CTE_1 A
JOIN CTE_2 B ON A.PARENT=B.ID)
SELECT * FROM CTE_2
ORDER BY ID
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/22793.html
標籤：基礎類
上一篇：這個是SQL陳述句還是資料庫代碼