怎么把上面的結果查詢成 下面這種形式：
shop_id image_url status image_url status
77901051 H:\File\11111.jpg 0 H:\File\2222.jpg 1
非常感謝 ！！！
uj5u.com熱心網友回復：
with t1 as
(select a*,row_number()over(partition by shop_id order by sysdate) as rn from table1)
select t1.shop_id,t1.image_url,0 as "status", tab1.image_url, 1 as "status1" from t1 where rn=1
join (select * from t1 where rn=2) tab1 on t1.shop_id =tab1.shop_id
uj5u.com熱心網友回復：
with v as(select t.*, row_number() over(partition by t.shopid order by t.status) rn
from t_shop t)
select v.shopid,
max(decode(v.rn, 1, v.image_url)) image_url_1,
'0' status_1,
max(decode(v.rn, 2, v.image_url)) image_url_2,
'1' status_2
from v
group by v.shopid;
-- 11g 特有pivot函式
with v as
(select shopid,
image_url,
row_number() over(partition by t.shopid order by t.status) rn
from t_shop t)
select v1.shopid,
v1."1_IMAGE_URL",
'0' status_1,
v1."2_IMAGE_URL",
'1' status_2
from (select * from v pivot(max(v.image_url) as image_url for rn in(1, 2))) v1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/74967.html
標籤：開發