看不到效果,来简化一下: create or replace view holeref_item_ston_v select rownum as row_num, holeref.hole_size, holeref.hole_count from holeref, item_ston_c where holeref.order_item_id = item_ston_c.order_item_id;select item_ston_c.*, (select hv.hole_size from holeref_item_ston_v hv where hv.row_num = 1) as hole_size_a, (select hv.hole_size from holeref_item_ston_v hv where hv.row_num = 2) as hole_size_b, (select hv.hole_size from holeref_item_ston_v hv where hv.row_num = 3) as hole_size_c, (select hv.hole_size from holeref_item_ston_v hv where hv.row_num = 4) as hole_size_d, (select hv.hole_count from holeref_item_ston_v hv where hv.row_num = 1) as hole_count_a, (select hv.hole_count from holeref_item_ston_v hv where hv.row_num = 2) as hole_count_b, (select hv.hole_count from holeref_item_ston_v hv where hv.row_num = 3) as hole_count_c, (select hv.hole_count from holeref_item_ston_v hv where hv.row_num = 4) as hole_count_d from item_ston_c where item_ston_c.ORDER_ITEM_ID = '2801';
语句太乱,整理了一下: select item_ston_c.* ,(select hole_size from (select rownum as id,holeref.hole_size from holeref,item_ston_c where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID) where id=1 ) as hole_size_a ,(select hole_size from (select rownum as id,holeref.hole_size from holeref,item_ston_c where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID) where id=2 ) as hole_size_b ,(select hole_size from (select rownum as id,holeref.hole_size from holeref,item_ston_c where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID) where id=3 ) as hole_size_c ,(select hole_size from (select rownum as id,holeref.hole_size from holeref,item_ston_c where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID) where id=4 ) as hole_size_d ,(select hole_count from (select rownum as id,holeref.hole_count from holeref,item_ston_c where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID) where id=1 ) as hole_count_a ,(select hole_count from (select rownum as id,holeref.hole_count from holeref,item_ston_c where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID) where id=2 ) as hole_count_b ,(select hole_count from (select rownum as id,holeref.hole_count from holeref,item_ston_c where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID) where id=3 ) as hole_count_c ,(select hole_count from (select rownum as id,holeref.hole_count from holeref,item_ston_c where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID) where id=4 ) as hole_count_d from item_ston_c where item_ston_c.ORDER_ITEM_ID='2801'不太明白楼主的意图是什么? 最终想要个什么样的结果?
create or replace view holeref_item_ston_v
select rownum as row_num, holeref.hole_size, holeref.hole_count
from holeref, item_ston_c
where holeref.order_item_id = item_ston_c.order_item_id;select item_ston_c.*,
(select hv.hole_size
from holeref_item_ston_v hv
where hv.row_num = 1) as hole_size_a,
(select hv.hole_size
from holeref_item_ston_v hv
where hv.row_num = 2) as hole_size_b,
(select hv.hole_size
from holeref_item_ston_v hv
where hv.row_num = 3) as hole_size_c,
(select hv.hole_size
from holeref_item_ston_v hv
where hv.row_num = 4) as hole_size_d,
(select hv.hole_count
from holeref_item_ston_v hv
where hv.row_num = 1) as hole_count_a,
(select hv.hole_count
from holeref_item_ston_v hv
where hv.row_num = 2) as hole_count_b,
(select hv.hole_count
from holeref_item_ston_v hv
where hv.row_num = 3) as hole_count_c,
(select hv.hole_count
from holeref_item_ston_v hv
where hv.row_num = 4) as hole_count_d
from item_ston_c
where item_ston_c.ORDER_ITEM_ID = '2801';
select item_ston_c.*
,(select hole_size
from (select rownum as id,holeref.hole_size
from holeref,item_ston_c
where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID)
where id=1
) as hole_size_a
,(select hole_size
from (select rownum as id,holeref.hole_size
from holeref,item_ston_c
where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID)
where id=2
) as hole_size_b
,(select hole_size
from (select rownum as id,holeref.hole_size
from holeref,item_ston_c
where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID)
where id=3
) as hole_size_c
,(select hole_size
from (select rownum as id,holeref.hole_size
from holeref,item_ston_c
where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID)
where id=4
) as hole_size_d
,(select hole_count
from (select rownum as id,holeref.hole_count
from holeref,item_ston_c
where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID)
where id=1
) as hole_count_a
,(select hole_count
from (select rownum as id,holeref.hole_count
from holeref,item_ston_c
where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID)
where id=2
) as hole_count_b
,(select hole_count
from (select rownum as id,holeref.hole_count
from holeref,item_ston_c
where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID)
where id=3
) as hole_count_c
,(select hole_count
from (select rownum as id,holeref.hole_count
from holeref,item_ston_c
where holeref.order_item_id=item_ston_c.ORDER_ITEM_ID)
where id=4
) as hole_count_d
from item_ston_c
where item_ston_c.ORDER_ITEM_ID='2801'不太明白楼主的意图是什么?
最终想要个什么样的结果?