那就随便选一个次序吧 SELECT a.name, b.col_a, b.col_b, b.col_c FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY age) AS rn FROM table_a ) a JOIN (SELECT *, ROW_NUMBER() OVER(ORDER BY col_a) AS rn FROM table_b ) b ON a.rn = b.rn name col_a col_b col_c ---- ----------- ----------- ----------- 张三 1 1 1 李四 2 2 2 王武 3 3 3
table_a表只有一个字段 , 我要用存储过程怎么改一下。
INSERT INTO table_c SELECT a.name, b.col_a, b.col_b, b.col_c FROM (SELECT name, ROW_NUMBER() OVER(ORDER BY name) AS rn FROM table_a ) a JOIN (SELECT *, ROW_NUMBER() OVER(ORDER BY col_a) AS rn FROM table_b ) b ON a.rn = b.rn
确定 不了 ,也可以 是
张三 对应 2 2 2
李四 对应 1 1 1
王武 对应 3 3 3
数据添加到 table_c
张三 2 2 2
李四 3 3 3
王武 1 1 1
或者
张三 3 3 3
李四 2 2 2
王武 1 1 1
SELECT a.name,
b.col_a,
b.col_b,
b.col_c
FROM (SELECT *,
ROW_NUMBER() OVER(ORDER BY age) AS rn
FROM table_a
) a
JOIN (SELECT *,
ROW_NUMBER() OVER(ORDER BY col_a) AS rn
FROM table_b
) b
ON a.rn = b.rn
name col_a col_b col_c
---- ----------- ----------- -----------
张三 1 1 1
李四 2 2 2
王武 3 3 3
SELECT a.name,
b.col_a,
b.col_b,
b.col_c
FROM (SELECT name,
ROW_NUMBER() OVER(ORDER BY name) AS rn
FROM table_a
) a
JOIN (SELECT *,
ROW_NUMBER() OVER(ORDER BY col_a) AS rn
FROM table_b
) b
ON a.rn = b.rn