表一
bh xlh mc
1 1101
2 1102
表二
xlh mc
1101 大
1101 单个单个
1102 你好
需要得到如下结果
表一
bh xlh mc
1 1101 1.大 2.单个单个
2 1102 你好
N条记录
表一mc字段只保留10个字符--多条结果组合后，有两条以上记录需要表上阿拉伯数字并1 2之间有一个空格
------解决思路----------------------
这样就可以了
WITH x AS
(SELECT 1 bh, 1101 xlh, NULL mc
FROM dual
UNION ALL
SELECT 2 bh, 1102 xlh, NULL mc FROM dual),
x1 AS
(SELECT 1101 xlh, '大' mc
FROM dual
UNION ALL
SELECT 1101 xlh, '单个单个' mc
FROM dual
UNION ALL
SELECT 1102 xlh, '你好' mc FROM dual)
SELECT xlh, listagg(mc, ' ') within GROUP(ORDER BY mc)
FROM (SELECT x.bh,
x.xlh,
CASE
WHEN COUNT(x1.mc) over(PARTITION BY x.xlh) > 1 THEN
row_number()
over(PARTITION BY x.xlh ORDER BY x1.mc)
------解决思路----------------------
'.'
------解决思路----------------------
x1.mc
ELSE
x1.mc
END mc
FROM x, x1
WHERE x.xlh = x1.xlh)
GROUP BY xlh
------解决思路----------------------
update 表一
set mc =
(select wmsys.wm_concat(mc)
from (select xlh, to_char(row_number() over(PARTITION BY xlh ORDER BY mc)
------解决思路----------------------
'.')
------解决思路----------------------
mc mc
from 表二) a
where xlh = 表一.xlh)
------解决思路----------------------
试一下，未测试
update 表1 set mc=(
select decode(count(1),1,max(mc),
wmsys.wm_concat(rownum
------解决思路----------------------
'.'
------解决思路----------------------
mc))
from 表2 where xlh=表1.xlh)
------解决思路----------------------
利用1楼的select语句修改的update语句
update 表1 set mc=(select mc from
(SELECT xlh, listagg(mc, ' ') within GROUP(ORDER BY mc) mc
FROM (SELECT xlh,
CASE
WHEN COUNT(mc) over(PARTITION BY xlh) > 1 THEN
row_number()
over(PARTITION BY xlh ORDER BY mc)
------解决思路----------------------
'.'
------解决思路----------------------
mc
ELSE
mc
END mc
FROM 表2)
GROUP BY xlh)
where xlh=表1.xlh)
------解决思路----------------------
都select 出来了，update 很简单啊 ，merge 下就好，但是我觉得，既然可以直接查询出来，没必要update了把，毕竟只是需要看数据