是这个意思吗 select translate(wm_concat(a),',.','-,') output from( select a||'.'||b a from test )
自己测试一下。 select substr(cn,2) T from ( select k,sys_connect_by_path(k,'-') cn,rn from (select A||','||B k,row_number()over(order by rowid) rn from Test) start with rn = 1 connect by rn = prior rn+1 order by rn desc ) where rownum < 2
SELECT T2.B || CASE WHEN T1.B IS NOT NULL AND T2.B IS NOT NULL THEN ' - ' END || T1.A FROM TEST T1 LEFT JOIN TEST T2 ON T1.B - 1 = T2.BUNIONSELECT T2.B || CASE WHEN T1.B IS NOT NULL AND T2.B IS NOT NULL THEN ' - ' END || T1.A FROM TEST T1 RIGHT JOIN TEST T2 ON T1.B - 1 = T2.B
SELECT R FROM (SELECT T1.A AS I, T2.B || CASE WHEN T1.B IS NOT NULL AND T2.B IS NOT NULL THEN ' - ' END || T1.A AS R FROM TEST T1 LEFT JOIN TEST T2 ON T1.B - 1 = T2.B
UNION
SELECT T1.A AS I, T2.B || CASE WHEN T1.B IS NOT NULL AND T2.B IS NOT NULL THEN ' - ' END || T1.A AS R FROM TEST T1 RIGHT JOIN TEST T2 ON T1.B - 1 = T2.B) ORDER BY I结果:A 1 - B 2 - C 3
[Quote=引用 6 楼 wildwave 的回复:] 是这个意思吗 SQL codeselect translate(wm_concat(a),',.','-,') outputfrom(select a||'.'||b afrom test ) [/Quot这个办法也不错,只是太大意,写错了!应该是这样那个:select translate(wm_concat(a),',.','-,') output from (select t.cname||'.'||t.cid a from testDB t )
这样?
select translate(wm_concat(a),',.','-,') output from(
select a||'.'||b a
from test )
select substr(cn,2) T from
(
select k,sys_connect_by_path(k,'-') cn,rn
from
(select A||','||B k,row_number()over(order by rowid) rn from Test)
start with rn = 1
connect by rn = prior rn+1
order by rn desc
)
where rownum < 2
FROM TEST T1
LEFT JOIN TEST T2
ON T1.B - 1 = T2.BUNIONSELECT T2.B || CASE WHEN T1.B IS NOT NULL AND T2.B IS NOT NULL THEN ' - ' END || T1.A
FROM TEST T1
RIGHT JOIN TEST T2
ON T1.B - 1 = T2.B
FROM (SELECT T1.A AS I,
T2.B || CASE
WHEN T1.B IS NOT NULL AND T2.B IS NOT NULL THEN
' - '
END || T1.A AS R
FROM TEST T1
LEFT JOIN TEST T2 ON T1.B - 1 = T2.B
UNION
SELECT T1.A AS I,
T2.B || CASE
WHEN T1.B IS NOT NULL AND T2.B IS NOT NULL THEN
' - '
END || T1.A AS R
FROM TEST T1
RIGHT JOIN TEST T2 ON T1.B - 1 = T2.B)
ORDER BY I结果:A
1 - B
2 - C
3
是这个意思吗
SQL codeselect translate(wm_concat(a),',.','-,') outputfrom(select a||'.'||b afrom test )
[/Quot这个办法也不错,只是太大意,写错了!应该是这样那个:select translate(wm_concat(a),',.','-,') output
from
(select t.cname||'.'||t.cid a from testDB t )