这样就可以了
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
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,而不是只是select
update 表1 set mc=(
select decode(count(1),1,max(mc),
wmsys.wm_concat(rownum||'.'||mc))
from 表2 where xlh=表1.xlh)
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)
我的目的是要更新数据,你没看到是两个表吗 嘻嘻谢谢大家