Posted on 2007-09-15 18:31
久城 閱讀(409)
評(píng)論(0) 編輯 收藏 所屬分類:
問(wèn)題
表如下：
表1
brand codekey content1
a 1 ccc
a 2 bbb
a 3 ddd
b 5 aaa
...
表2
brand codekey content2
a 1 xxx
a 1 yyy
a 2 zzz
b 8 hhh
...
我想查詢的是，當(dāng)條件brand = 'a' 時(shí)，查詢結(jié)果如下：
brand codekey content
a 1 cccxxxyyy
a 2 bbbzzz
a 3 ddd
解釋一下，返回的結(jié)果是key中不應(yīng)該有重復(fù)的，對(duì)應(yīng)的content列是相應(yīng)的codekey的content1中的所有與content2中所有的字符串連接。
問(wèn)題已解決，貼出解決辦法，以供參考：
select brand,
codekey,
replace(path, ' ', '') as content
from ( select brand,
codekey,
max(SYS_CONNECT_BY_PATH(content, ' ')) as path
from ( select brand,
codekey,
content,
(row_number() over(order by brand, codekey, content) + dense_rank() over(order by brand, codekey)) rn,
min(content) over(partition by brand, codekey) content1
from ( select brand,
codekey,
content1 as content
from table1
union all select brand,
codekey,
content2 as content
from table2
order by brand, codekey
)
)
start with content = content1
connect by prior rn = rn - 1
group by brand, codekey
having brand = 'a'
)歡迎來(lái)訪！^.^!
本BLOG僅用于個(gè)人學(xué)習(xí)交流!
目的在于記錄個(gè)人成長(zhǎng).
所有文字均屬于個(gè)人理解.
如有錯(cuò)誤，望多多指教！不勝感激！