表结构和内容如下
Name--Age--NO--Re
A--11--11--ABC
A--11--11--CCC
A--11--11--AAA
B--11--11--CCC
B--11--11--DDD
B--11--11--[NULL]
B--11--11--[NULL]
=======================================================
查询后的结果为
Name--Age--NO--Re
A--11--11--ABC,CCC,CCC
B--11--11--CCC,DDD
B--11--11--[NULL]就是前面三列数据重复的合并,后面一列不同的串起来。
重复的可能是2行,3行,或是更多行,求解。
补充一句,最后一列Re会有NULL值,和前面是同样的处理方式,前三列合并,后面的就不用串起来了,还是写NUll
Name--Age--NO--Re
A--11--11--ABC
A--11--11--CCC
A--11--11--AAA
B--11--11--CCC
B--11--11--DDD
B--11--11--[NULL]
B--11--11--[NULL]
=======================================================
查询后的结果为
Name--Age--NO--Re
A--11--11--ABC,CCC,CCC
B--11--11--CCC,DDD
B--11--11--[NULL]就是前面三列数据重复的合并,后面一列不同的串起来。
重复的可能是2行,3行,或是更多行,求解。
补充一句,最后一列Re会有NULL值,和前面是同样的处理方式,前三列合并,后面的就不用串起来了,还是写NUll
B--11--11--CCC
B--11--11--DDD
B--11--11--[NULL]
B--11--11--[NULL]
WITH t AS (
SELECT 'A' name,'11' age,'11' no,'ABC' re FROM dual
UNION ALL
SELECT 'A' name,'11' age,'11' no,'CCC' re FROM dual
UNION ALL
SELECT 'A' name,'11' age,'11' no,'AAA' re FROM dual
UNION ALL
SELECT 'B' name,'11' age,'11' no,'CCC' re FROM dual
UNION ALL
SELECT 'B' name,'11' age,'11' no,'DDD' re FROM dual
UNION ALL
SELECT 'B' name,'11' age,'11' no,NULL re FROM dual
UNION ALL
SELECT 'B' name,'11' age,'11' no,NULL re FROM dual
)SELECT name,age,no,wm_concat(re) FROM t GROUP BY name,age,no;
B--11--11--CCC
B--11--11--DDD
B--11--11--[NULL]
B--11--11--[NULL]
WITH t AS (
SELECT 'A' name,'11' age,'11' no,'ABC' re FROM dual
UNION ALL
SELECT 'A' name,'11' age,'11' no,'CCC' re FROM dual
UNION ALL
SELECT 'A' name,'11' age,'11' no,'AAA' re FROM dual
UNION ALL
SELECT 'B' name,'11' age,'11' no,'CCC' re FROM dual
UNION ALL
SELECT 'B' name,'11' age,'11' no,'DDD' re FROM dual
UNION ALL
SELECT 'B' name,'11' age,'11' no,NULL re FROM dual
UNION ALL
SELECT 'B' name,'11' age,'11' no,NULL re FROM dual
)SELECT Decode(temp.NAME,'z%',(SELECT DISTINCT NAME FROM t WHERE age=temp.age AND no=temp.no AND re IS NULL),temp.name )name,age,no,re
FROM
(
SELECT name,age,no,wm_concat(re)re FROM (SELECT Decode(re,NULL,'z%',name)name ,age,no,re FROM t) GROUP BY name,age,no
) temp