日期:2014-05-17  浏览次数:20949 次

求一小小的SQL语句
表如下:
表1
brand     key     content1
a             1         ccc
a             2         bbb
a             3         ddd
b             5         aaa
...
表2
brand     key     content2
a             1         xxx
a             1         yyy
a             2         zzz
b             8         hhh
...

我想查询的是,当条件brand   =   a   时,查询结果如下:
brand     key     content
a             1         cccxxxyyy
a             2         bbbzzz
a             3         ddd

解释一下,返回的结果是key中不应该有重复的,对应的content列是相应的key的content1中的所有与content2中所有的字符串连接。

不需要一条语句,求出来就成,请教了,谢谢。

------解决方案--------------------
select brand,key,replace(path, ' ', ' ') as content from
(SELECT brand,key,max(SYS_CONNECT_BY_PATH(content, ' ')) as path
FROM
(select brand,key,content,
(row_number() over (order by brand,key,content)+dense_rank() over(order by brand,key)) rn,
min(content) over(partition by brand,key) content1
from
(select brand,key,content1 as content from table1
union all
select brand,key,content2 as content from table2
order by brand,key))
START WITH content = content1
CONNECT BY PRIOR rn = rn-1
group by brand,key)