Posted on 2007-09-15 18:31
久城 閱讀(416)
評論(0) 編輯 收藏 所屬分類:
問題
表如下：
表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
...
我想查詢的是，當條件brand = 'a' 時，查詢結果如下：
brand codekey content
a 1 cccxxxyyy
a 2 bbbzzz
a 3 ddd
解釋一下，返回的結果是key中不應該有重復的，對應的content列是相應的codekey的content1中的所有與content2中所有的字符串連接。
問題已解決，貼出解決辦法，以供參考：
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'
)歡迎來訪！^.^!
本BLOG僅用于個人學習交流!
目的在于記錄個人成長.
所有文字均屬于個人理解.
如有錯誤，望多多指教！不勝感激！