create table test(Row int,val int)
insert into test select 1,101
insert into test select 1,102
insert into test select 2,1
insert into test select 3,2
select a.row,b.val from (
select distinct row from test) a,(
select distinct val from test a where not exists(select 1 from test where row=a.val)
)b
go
drop table test
/*
row val
----------- -----------
1 101
2 101
3 101
1 102
2 102
3 102*/
insert into test select 1,101
insert into test select 1,102
insert into test select 2,1
insert into test select 3,2
select a.row,b.val from (
select distinct row from test) a,(
select distinct val from test a where not exists(select 1 from test where row=a.val)
)b
go
drop table test
/*
row val
----------- -----------
1 101
2 101
3 101
1 102
2 102
3 102*/
insert into test select 1,101
insert into test select 1,102
insert into test select 2,1
insert into test select 3,2
select a.row,b.val from (
select distinct row from test) a,(
select distinct val from test a where not exists(select 1 from test where row=a.val)
)b order by a.row
go
drop table test
/*
row val
----------- -----------
1 101
1 102
2 102
2 101
3 101
3 102
*/
go
create table [tb]([Row] int,[val] int)
insert [tb]
select 1,101 union all
select 1,102 union all
select 2,1 union all
select 3,2
go
--select * from [tb];with szx as
(
select row,val from tb where row=1
union all
select a.row,b.val from tb a join szx b on a.val=b.row
)
select row,val from szx order by row,val
/*
row val
----------- -----------
1 101
1 102
2 101
2 102
3 101
3 102(6 行受影响)*/
declare @T table(Row int, val int)
insert into @T
SELECT 1, 101 UNION ALL
SELECT 1, 102 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2 ;with cte as
(
select *,firstNode = row,px = row_number() over(order by row) from @T a where not exists(select 1 from @T where a.val = row)
union all
select a.*,b.firstNode,b.px from @T a,cte b where a.val = b.row
)select * into # from cteupdate # set val = b.val from # a,(select * from # a where not exists(select 1 from # where a.val = row))b
where a.px = b.px and a.firstNode = b.firstNode
select row,val from # order by row,valdrop table #/*
1 101
1 102
2 101
2 102
3 101
3 102
*/
create table test(Row int,val int)
insert into test select 1,101
insert into test select 1,102
insert into test select 2,1
insert into test select 3,2
select a.row,b.val from (
select distinct row from test) a,(
select distinct val from test a where not exists(select 1 from test where row=a.val)
)b
go
drop table test
/*
row val
----------- -----------
1 101
2 101
3 101
1 102
2 102
3 102*/