Posted on 2007-09-15 18:31
久城 阅读(392)
评论(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仅用于个人学习交流!
目的在于记录个人成长.
所有文字均属于个人理解.
如有错误，望多多指教！不胜感激！