原数据
合并后数据
按name字段分组,合并house,aaa字段*
合并数据后的缺点就是有重复的的,这个是本人接受不了的,限于技术有限,只能凑合的用了,业务要求多,技术不过关哎。。。
建表
CREATE TABLE ttable
( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ,
name varchar(100),
house varchar(100),
aaa varchar(100),
PRIMARY KEY (id)
);
INSERT INTO ttABLE(name,house,aaa)
values('张三','美丽','啊'),
('张三','美丽','艾克'),
('张三','天使1','啊'),
('李四','蓝天3','看看'),
('张三','定时','第三方'),
('李四','是否','看看'),
('李四','白云1','中');
方法一:
SELECT
MAX(ID),name,
REPLACE(REPLACE(xml2clob(xmlagg(xmlelement(name A, house || ','))), '<A>', ''), '</A>', '') house,
REPLACE(REPLACE(xml2clob(xmlagg(xmlelement(name A, aaa || ','))), '<A>', ''), '</A>', '') aaa
FROM ttABLE
GROUP BY name
方法二:
--给数据加上序号
with wa_RowNum(id,name,house,aaa,rn) as(
select id,name,house,aaa,ROW_NUMBER() OVER(PARTITION BY name) from TTABLE
),
--CTE递归拼接house,序号最大的行拼接了所有的house数据
wa_addHouse(id,name,house,aaa,rn) as(
select wr.id,wr.name,CAST(wr.house AS CLOB),CAST(wr.aaa AS CLOB),wr.rn from wa_RowNum wr where wr.rn=1
UNION ALL
select wrn.id,wrn.name,CAST(wah.house||';'||wrn.house AS CLOB),CAST(wah.aaa||';'||wrn.aaa AS CLOB),wah.rn+1
from wa_addHouse wah,wa_RowNum wrn where wrn.name=wah.name and wah.rn+1=wrn.rn
),
--过滤掉不需要的数据
wa_getMax(id,name,house,aaa) as(
select id,name,house,aaa from wa_addHouse wa
where wa.rn in(
select max(iwa.rn) from wa_addHouse iwa where iwa.name=wa.name)
)
select * from wa_getMax