22,206
社区成员
发帖
与我相关
我的任务
分享
create table T1(aid int, aname varchar(5))
create table T2(bid int, bname varchar(5))
insert into T1
values(1,'a'),(3,'b'),(10,'c')
insert into T2
values(1,'aa'),(1,'aa'),(3,'b'),(10,'c'),(10,'c')
with t as
( select aid,aname,
row_number() over(order by aid) rn from t1
)
update b
set b.bid=a.rn
from T2 b
inner join t a on b.bid=a.aid;
with t as
( select aid,aname,
row_number() over(order by aid) rn from T1
)
update a
set a.aid=b.rn
from T1 a
inner join t b on a.aid=b.aid;
select * from T1
/*
aid aname
----------- -----
1 a
2 b
3 c
(3 row(s) affected)
*/
select * from T2
/*
bid bname
----------- -----
1 aa
1 aa
2 b
3 c
3 c
(5 row(s) affected)
*/
CREATE TABLE t1 (id int ,NAME varchar(10))
INSERT INTO t1
SELECT 1,'a'
UNION ALL
SELECT 3,'b'
UNION ALL
SELECT 10,'c'
CREATE TABLE t2(id int ,NAME varchar(10))
INSERT INTO t2
SELECT 1,'aa'
UNION ALL
SELECT 1,'aa'
UNION ALL
SELECT 3,'b'
UNION ALL
SELECT 10,'c'
UNION ALL
SELECT 10,'c'
SELECT * FROM t1 a LEFT JOIN t2 b ON A.id=b.id
ORDER BY a.id
/*
id NAME id NAME
----------- ---------- ----------- ----------
1 a 1 aa
1 a 1 aa
3 b 3 b
10 c 10 c
10 c 10 c
(5 行受影响)
*/
select t1.*,t2.* from t1,t2 where t1.aid=t2.bid
order by t1.aid,t2.bid