大哥是这样的我的SQL(真实的): select b.CustomerOrderNo CustomerOrderNo
from ORDER_MAPPING a
left join CUSTOMER_ORDER b
on a.CustomerOrderID = b.CustomerOrderID
where CMTOrderNo ='JCHI-1000'它显示的值为:
CustomerOrderNo
nnnnn
bbbbb
.....
.....
它的行数是不定的.我要如何用一条SQL把它变成: nnnnn,bbbbb,....,....
请大侠指教!请大侠根据实际的表来写:
select b.CustomerOrderNo CustomerOrderNo
from ORDER_MAPPING a
left join CUSTOMER_ORDER b
on a.CustomerOrderID = b.CustomerOrderID
where CMTOrderNo ='JCHI-1000'在线等谢谢!!
from ORDER_MAPPING a
left join CUSTOMER_ORDER b
on a.CustomerOrderID = b.CustomerOrderID
where CMTOrderNo ='JCHI-1000'它显示的值为:
CustomerOrderNo
nnnnn
bbbbb
.....
.....
它的行数是不定的.我要如何用一条SQL把它变成: nnnnn,bbbbb,....,....
请大侠指教!请大侠根据实际的表来写:
select b.CustomerOrderNo CustomerOrderNo
from ORDER_MAPPING a
left join CUSTOMER_ORDER b
on a.CustomerOrderID = b.CustomerOrderID
where CMTOrderNo ='JCHI-1000'在线等谢谢!!
解决方案 »
- 请问为什么分类表和词条表之间要有个词条和分类的关联表
- oracle9i 调优 statspack报告分析,请各位大侠帮帮忙啊,以前从没做过
- oracle 10g listener问题
- 两个SELECT语句执行后产生两个结果集,想将这两个结果集合并成一个结果集
- 怎样设置网络归档路径?
- Oracle10g isqlplus SYS登录问题
- 关于表空间的问题
- 在oracle下载的三个文件,为什么只有几K大小?
- oracle9i中怎样查看所有Enterprise Manager Console操作的SQL语句?
- oracle一次删除20000条记录会自动停止数据库?
- pb里oracle问题
- 关于触发器问题
大哥~~
--------------------
nnnnn
bbbbb
ccccc
aaaaa
ggggg
SQL> select substr(max(sys_connect_by_path(field1, ',')), 2) from (
2 select field1, t1, lead(t1) over(order by t1) t2 from (
3 select a.field1, row_number() over(order by field1) t1 from a))
4 start with t2 is null
5 connect by prior t1=t2;SUBSTR(MAX(SYS_CONNECT_BY_PATH(FIELD1,',')),2)
-------------------------------------------------------------------------
nnnnn,ggggg,ccccc,bbbbb,aaaaa
select b.CustomerOrderNo CustomerOrderNo
from ORDER_MAPPING a
left join CUSTOMER_ORDER b
on a.CustomerOrderID = b.CustomerOrderID
where CMTOrderNo ='JCHI-1000'把 field1 替换成 CustomerOrderNo 就可以了或
SQL> select substr(max(sys_connect_by_path(field1, ',')), 2) from (
2 select field1, t1, lead(t1) over(order by t1) t2 from (
3 select a.field1, row_number() over(order by field1) t1 from (
select b.CustomerOrderNo field1
from ORDER_MAPPING a
left join CUSTOMER_ORDER b
on a.CustomerOrderID = b.CustomerOrderID
where CMTOrderNo ='JCHI-1000')))
4 start with t2 is null
5 connect by prior t1=t2;
--------------------
1
2
3
select substr(max(sys_connect_by_path(a,',')),2) result
from (select a,rownum rn from test_1)
start with rn = 1
connect by rn = prior rn+1;