日期:2014-05-17  浏览次数:20921 次

关于建立视图的主键问题!求教
现在业务需要建个视图
但建立的视图,每一条数据都没有一个字段可以用来作为唯一标示来区别这条数据。


请问如何解决?
可以增加个序列吗?如果可以,怎么写?

附上视图脚本:

SQL code
CREATE OR REPLACE VIEW v_Load_Dis_Charge_info
(    
    nums,
    vessel_m,
    voyage_n,
    size_q,
    operator_c,
    status_c,
    type_c,
    inland_trade_i,
    shipping_status_c
 ) 
AS 


 select 
 
 
 count(*) as nums,
 loading_vessel_m,
 LOADING_VOYAGE_N,
 size_q,
 out_slot_operator_c,
 status_c,
 type_c,
 inland_trade_i,
 '出口' as shipping_status_c
 from cntr 
 where  shipping_status_c in ('EX','RE','RS') 

 group by out_slot_operator_c,size_q,status_c,type_c,inland_trade_i,loading_vessel_m,LOADING_VOYAGE_N
 

union

 select 
 
 count(*) as nums,
 discharge_vessel_m,
 discharge_VOYAGE_N,
 size_q,
 in_slot_operator_c,
 status_c,
 type_c,
 inland_trade_i,
 '进口' as shipping_status_c
 from cntr 
 where  shipping_status_c in ('IM','OL') 
 group by in_slot_operator_c,size_q,status_c,type_c,inland_trade_i,discharge_vessel_m,discharge_VOYAGE_N


------解决方案--------------------
不可以增加序列,因为视图是通过表建成的。他的列都属于表里的列
你可以通过一张表里的序列号来辨别数据
------解决方案--------------------
我的建议是你把group by的所有字段当成联合主键。
------解决方案--------------------
或者
select 
 nums,
 loading_vessel_m,
 LOADING_VOYAGE_N,
 size_q,
 out_slot_operator_c,
 status_c,
 type_c,
 inland_trade_i,
 shipping_status_c,
 rownum as id
from (
select 
 
 
 count(*) as nums,
 loading_vessel_m,
 LOADING_VOYAGE_N,
 size_q,
 out_slot_operator_c,
 status_c,
 type_c,
 inland_trade_i,
 '出口' as shipping_status_c
 from cntr 
 where shipping_status_c in ('EX','RE','RS') 

 group by out_slot_operator_c,size_q,status_c,type_c,inland_trade_i,loading_vessel_m,LOADING_VOYAGE_N
 

union

 select 
 
 count(*) as nums,
 discharge_vessel_m,
 discharge_VOYAGE_N,
 size_q,
 in_slot_operator_c,
 status_c,
 type_c,
 inland_trade_i,
 '进口' as shipping_status_c
 from cntr 
 where shipping_status_c in ('IM','OL') 
 group by in_slot_operator_c,size_q,status_c,type_c,inland_trade_i,discharge_vessel_m,discharge_VOYAGE_N
)
------解决方案--------------------
探讨
引用:
你最后加个rownum即可.


加了这个,groupby里也要加rownum,这样就起不到作用了。