求SQL语句,表及测试数据如下:
CREATE TABLE TEST_SHIPMENT
(
rkey INT NOT NULL,--自动编号
shipid VARCHAR(32) NULL, --装运单号
box NUMBER(10) NULL, --装运箱数
qty NUMBER(10) NULL --每箱数量
)
INSERT INTO TEST_SHIPMENT
SELECT 1,'0001',10,600 FROM DUAL
UNION ALL SELECT 2,'0001',1,200 FROM DUAL
UNION ALL SELECT 3,'0002',10,1000 FROM DUAL
UNION ALL SELECT 4,'0003',12,500 FROM DUAL
UNION ALL SELECT 5,'0003',1,400 FROM DUAL--SELECT * FROM TEST_SHIPMENT
我想得到如下结果:(最好用一条SQL语句实现,不用Function及Cursor)
SHIPID(装运单号) BOX
----------------------------------
0001 10*600,1*200
0002 10*1000
0003 12*500,1*400
CREATE TABLE TEST_SHIPMENT
(
rkey INT NOT NULL,--自动编号
shipid VARCHAR(32) NULL, --装运单号
box NUMBER(10) NULL, --装运箱数
qty NUMBER(10) NULL --每箱数量
)
INSERT INTO TEST_SHIPMENT
SELECT 1,'0001',10,600 FROM DUAL
UNION ALL SELECT 2,'0001',1,200 FROM DUAL
UNION ALL SELECT 3,'0002',10,1000 FROM DUAL
UNION ALL SELECT 4,'0003',12,500 FROM DUAL
UNION ALL SELECT 5,'0003',1,400 FROM DUAL--SELECT * FROM TEST_SHIPMENT
我想得到如下结果:(最好用一条SQL语句实现,不用Function及Cursor)
SHIPID(装运单号) BOX
----------------------------------
0001 10*600,1*200
0002 10*1000
0003 12*500,1*400
from (select shipid,box,qty,
(row_number() over(order by shipid,box desc)
+ dense_rank() over(order by shipid)) rn,
max(box) over(partition by shipid) qs
from TEST_SHIPMENT
)
start with box = qs
connect by rn-1 = prior rn
group by shipid
http://community.csdn.net/Expert/TopicView1.asp?id=4161213
用length不知道怎么做了。
select shipid,substr(max(sys_connect_by_path(box || '*' || qty,',')),2,1000) box
from (select shipid,box,qty,
(row_number() over(order by shipid,box desc)
+ dense_rank() over(order by shipid)) rn,
max(box) over(partition by shipid) qs
from TEST_SHIPMENT
)
start with box = qs
connect by rn-1 = prior rn
group by shipid
但我如果想把10*600,1*200里面的”,”换成CHR(13)也就是换行符。应该如何处理,因为我是做报表。如果太长了。会显示不完全,所以我想用换行符
但我如果想把10*600,1*200里面的"," 换成CHR(13)也就是换行符。应该如何处理,因为我是做报表。如果太长了。会显示不完全,所以我想用换行符
-----
dd
ee
Executed in 0.015 secondsSQL>
-----
dd,eeExecuted in 0 secondsSQL> select replace('dd,ee',',',chr(13)) Str from dual;STR
-----
dd
ee
Executed in 0.031 secondsSQL>
select shipid, t1, t2
from (select row_number() over(partition by shipid order by shipid) as rn,
t. *
from (select SHIPID,
box || '*' || qty as t1,
lead(box || '*' || qty, 1) over(partition by shipid order by shipid) as t2
from TEST_SHIPMENT) t)
where rn = 1)
--------------
ABCDEFGHIJK...Executed in 0.016 secondsSQL> select substr('ABCDEFGHIJK...',1,7)||chr(13)||substr('ABCDEFGHIJK...',8,7) Str from dual;STR
---------------
ABCDEFG
HIJK...
Executed in 0.016 secondsSQL>