如何在oracle中合并两行

Question

我有一个有多条记录的表。一条记录有一些数据，另一条记录有一些数据和更新的数据

5000

Answer 1

您应该能够使用这些语句清理额外的记录(ID 2)。请确保将下面语句中的your_table替换为实际的表名。

UPDATE your_table t1
SET (name,
gender,
birthday,
address,
salary) =
(SELECT nvl(t2.name, t1.name),
nvl(t2.gender, t1.gender),
nvl(t2.birthday, t1.birthday),
nvl(t2.address, t1.address),
nvl(t2.salary, t1.salary)
FROM your_table t2
WHERE t2.id = 2)
WHERE t1.id = 1;
DELETE FROM your_table
WHERE id = 2;

Answer 2

如果您想优先考虑最新的id:

select distinct
last_value(name ignore nulls) over (order by id desc) as name,
last_value(gender ignore nulls) over (order by id desc) as gender,
last_value(birthday ignore nulls) over (order by id desc) as birthday,
last_value(address ignore nulls) over (order by id desc) as address,
last_value(salary ignore nulls) over (order by id desc) as salary
from t;