如果行与行之间的唯一区别是空值与非空值,则可以在连接之前使用聚合:
-- CTE for sample data
with "#TEMP" (city, country, state, id) as (
select 'Great Falls', null, 'VA', 12345 from dual
union all select 'Great Falls', 'USA', 'VA', 12345 from dual
)
-- actual query
select max(city) as city, max(country) as country, max(state) as state,
max(city) ||', ' ||max(country)|| ', '||max(state) as concat_fields
from "#TEMP"
group by id;
CITY COU ST CONCAT_FIELDS
----------- --- -- --------------------
Great Falls USA VA Great Falls, USA, VA
如果ID的行具有不同的非空值,那么这显然不起作用:
with "#TEMP" (city, country, state, id) as (
select 'Great Falls', null, 'VA', 12345 from dual
union all select 'London', 'UK', null, 12345 from dual
)
select max(city) as city, max(country) as country, max(state) as state,
max(city) ||', ' ||max(country)|| ', '||max(state) as concat_fields
from "#TEMP";
CITY CO ST CONCAT_FIELDS
----------- -- -- -------------------
London UK VA London, UK, VA
但是,您需要某种方法来确定哪一行是正确的-例如,如果这代表客户地址的历史记录,并且您需要“最新”版本,则从序列或日期列中确定。接下来的诀窍是知道如何确定哪个是“最新的”。