| |

VerySource

 Forgot password?
 Register
Search
View: 1113|Reply: 3

Ask every master a SQL article

[Copy link]

1

Threads

1

Posts

2.00

Credits

Newbie

Rank: 1

Credits
2.00

 Japan

Post time: 2020-12-15 19:00:01
| Show all posts |Read mode
Table as:
id par root
1 0 A
twenty one      
3 1
4 0 ss
5 4 B
6 4
7 2 C
8 2

Use SQL to realize that the root value of par non-zero record is consistent with the root value of id equal to its par.
Reply

Use magic Report

0

Threads

48

Posts

30.00

Credits

Newbie

Rank: 1

Credits
30.00

 China

Post time: 2020-12-15 21:15:01
| Show all posts
Just execute it a few more times
update a set a.root=b.root from tablename a join tablename b on a.par=b.id
Reply

Use magic Report

0

Threads

88

Posts

55.00

Credits

Newbie

Rank: 1

Credits
55.00

 China

Post time: 2020-12-16 00:15:01
| Show all posts
update a
set a.root=b.root
from table A a, table A b
where a.par=b.id
Reply

Use magic Report

0

Threads

48

Posts

30.00

Credits

Newbie

Rank: 1

Credits
30.00

 China

Post time: 2020-12-16 01:15:01
| Show all posts
create function get_root(@id varchar(10))
returns varchar(10)
as
begin
   declare @str varchar(10)
   declare @i varchar(10)
   select @str=id,@i=par from tablename where id=@id
   while @i<>'0'
         select @str=id,@i=par from tablename where id=@i
   select @str=root from tablename where id=@str
  return(@str)
end
go
select dbo.get_root('7')
update tablename set root=dbo.get_root(id)
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list