现在有这么一些数据:
flag code price orderno
0 cathy 1 001
0 tippy 2 001
1 tippy 5 001
/**********************************/
0 cathy 1 002
1 cathy 4 002
1 tippy 5 002
/**********************************/
0 cathy 1 003
0 tippy 2 003
1 cathy 4 003
1 tippy 5 003
将同样orderno显示成一行数据.结果如下:
orderno flag0 code0 price0 flag1 code1 price1 flag2 code2 price2 flag3 code3 price3
001 0 cathy 1 0 tippy 2 1 tippy 5
002 0 cathy 1 1 cathy 4 1 tippy 5
003 0 cathy 1 0 tippy 2 1 cathy 4 1 tippy 5
注意001中少一条1 cathy 4 001,
注意002中少一句0 tippy 2 002,所以中间空着.
每个orderno最多四条记录.
ORACLE中有没有办法快速实现?谢谢了.
flag code price orderno
0 cathy 1 001
0 tippy 2 001
1 tippy 5 001
/**********************************/
0 cathy 1 002
1 cathy 4 002
1 tippy 5 002
/**********************************/
0 cathy 1 003
0 tippy 2 003
1 cathy 4 003
1 tippy 5 003
将同样orderno显示成一行数据.结果如下:
orderno flag0 code0 price0 flag1 code1 price1 flag2 code2 price2 flag3 code3 price3
001 0 cathy 1 0 tippy 2 1 tippy 5
002 0 cathy 1 1 cathy 4 1 tippy 5
003 0 cathy 1 0 tippy 2 1 cathy 4 1 tippy 5
注意001中少一条1 cathy 4 001,
注意002中少一句0 tippy 2 002,所以中间空着.
每个orderno最多四条记录.
ORACLE中有没有办法快速实现?谢谢了.
orderno
, (select flag from test where orderno = t.orderno and price = 1) flag0
, (select code from test where orderno = t.orderno and price = 1) code0
, 1 price0
, (select flag from test where orderno = t.orderno and price = 2) flag1
, (select code from test where orderno = t.orderno and price = 2) code1
, 2 price0
, (select flag from test where orderno = t.orderno and price = 4) flag2
, (select code from test where orderno = t.orderno and price = 4) code2
, 4 price0
, (select flag from test where orderno = t.orderno and price = 5) flag3
, (select code from test where orderno = t.orderno and price = 5) code3
, 5 price0
from test t
group by orderno
order by orderno
max(decode(tt.price,1,tt.flag,null)) as flag0,
max(decode(tt.price,1,tt.code,null)) as code0,
max(decode(tt.price,1,tt.price,null)) as price0,
max(decode(tt.price,2,tt.flag,null)) as flag1,
max(decode(tt.price,2,tt.code,null)) as code1,
max(decode(tt.price,2,tt.price,null)) as price1,
max(decode(tt.price,4,tt.flag,null)) as flag2,
max(decode(tt.price,4,tt.code,null)) as code2,
max(decode(tt.price,4,tt.price,null)) as price2,
max(decode(tt.price,5,tt.flag,null)) as flag3,
max(decode(tt.price,5,tt.code,null)) as code3,
max(decode(tt.price,5,tt.price,null)) as price3
from tablename tt
group by tt.orderno;
ORDERNO FLAG0 CODE0 PRICE0 FLAG1 CODE1 PRICE1 FLAG2 CODE2 PRICE2 FLAG3 CODE3 PRICE3
------- ---------- ----- ---------- ---------- ----- ---------- ---------- ----- ---------- ---------- ----- ----------
001 0 cathy 1 0 tippy 2 1 tippy 5
002 0 cathy 1 0 cathy 4 1 tippy 5
003 0 cathy 1 0 tippy 2 1 cathy 4 1 tippy 5