oracle 连接字段,如何在Oracle中连接字段?

如果行与行之间的唯一区别是空值与非空值,则可以在连接之前使用聚合:

-- 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

但是,您需要某种方法来确定哪一行是正确的-例如,如果这代表客户地址的历史记录,并且您需要“最新”版本,则从序列或日期列中确定。接下来的诀窍是知道如何确定哪个是“最新的”。