谁能提供一个创建复杂视图的语句，给俺参考一下？

Question

搜一搜网上有不少的
http://www.zdnet.com.cn/developer/code/story/0,2000081534,39156017,00.htm

Answer 1

CREATE OR REPLACE VIEW V_CH_HISTORYPERSON
    (NAME,ID_NAME_MC,ID_CODE,HOTEL_MC,CODE,NO_ROOM,IN_TIME,OUT_TIME,GUEST_ID,
    STA_CODE_MC,BUR_CODE_MC,SEX,BDATE,NATION,XZQH,ADDRESS)
AS
SELECT DISTINCT
 NAME,T_ID_NAME.CALLED,ID_CODE,T_HOTEL.CALLED,T_HOTEL.CODE,NO_ROOM,
 substr(IN_TIME,1,4)||'/'||substr(in_time,5,2)||'/'||substr(in_time,7,2)||' '||substr(in_time,9,2)||':'||substr(in_time,11,2),
 substr(OUT_TIME,1,4)||'/'||substr(out_time,5,2)||'/'||substr(out_time,7,2)||' '||substr(out_time,9,2)||':'||substr(out_time,11,2),
 ID,t_station.called,t_bureau.called,t_sex.called,
 substr(bdate,1,4)||'/'||substr(bdate,5,2)||'/'||substr(bdate,7,2),
  t_nation.called,t_xzqh.called,t_ch_pre.address
FROM
 T_CH_PRE,T_ID_NAME,T_HOTEL,t_station,t_bureau,t_sex,t_nation,t_xzqh
WHERE
 SUBSTR(ID,1,10)=T_HOTEL.CODE (+) AND
 ID_NAME=T_ID_NAME.CODE (+) and
 t_ch_pre.sta_code=t_station.code (+) and
 t_ch_pre.bur_code=t_bureau.code (+) and
 sex=t_sex.code (+) and
 nation=t_nation.code (+) and xzqh=t_xzqh.code (+)
UNION
SELECT DISTINCT
 NAME,T_ID_NAME.CALLED,ID_CODE,T_HOTEL.CALLED,T_HOTEL.CODE,NO_ROOM,
  substr(IN_TIME,1,4)||'/'||substr(in_time,5,2)||'/'||substr(in_time,7,2)||' '||substr(in_time,9,2)||':'||substr(in_time,11,2),
  substr(OUT_TIME,1,4)||'/'||substr(out_time,5,2)||'/'||substr(out_time,7,2)||' '||substr(out_time,9,2)||':'||substr(out_time,11,2),
 ID,t_station.called,t_bureau.called,t_sex.called,
 substr(bdate,1,4)||'/'||substr(bdate,5,2)||'/'||substr(bdate,7,2),
  t_nation.called,t_xzqh.called,t_ch_rec.address
FROM
 T_CH_REC,T_ID_NAME,T_HOTEL,t_station,t_bureau,t_sex,t_nation,t_xzqh
WHERE
 SUBSTR(ID,1,10)=T_HOTEL.CODE (+) AND
 ID_NAME=T_ID_NAME.CODE (+) and
 t_ch_rec.sta_code=t_station.code (+) and
 t_ch_rec.bur_code=t_bureau.code (+) and
 sex=t_sex.code (+) and
 nation=t_nation.code (+) and
 xzqh=t_xzqh.code (+)
UNION
SELECT DISTINCT
 NAME,T_ID_NAME.CALLED,ID_CODE,T_HOTEL.CALLED,T_HOTEL.CODE,NO_ROOM,
 substr(IN_TIME,1,4)||'/'||substr(in_time,5,2)||'/'||substr(in_time,7,2)||' '||substr(in_time,9,2)||':'||substr(in_time,11,2),
  substr(OUT_TIME,1,4)||'/'||substr(out_time,5,2)||'/'||substr(out_time,7,2)||' '||substr(out_time,9,2)||':'||substr(out_time,11,2),
 ID,t_station.called,t_bureau.called,t_sex.called,
 substr(bdate,1,4)||'/'||substr(bdate,5,2)||'/'||substr(bdate,7,2),
 t_nation.called,t_xzqh.called,t_ch_his.address
FROM
 T_CH_HIS,T_ID_NAME,T_HOTEL,t_station,t_bureau,t_sex,t_nation,t_xzqh
WHERE
 SUBSTR(ID,1,10)=T_HOTEL.CODE (+) AND
 ID_NAME=T_ID_NAME.CODE (+) and
 t_ch_his.sta_code=t_station.code (+) and
 t_ch_his.bur_code=t_bureau.code (+) and
 sex=t_sex.code (+) and
 nation=t_nation.code (+) and
 xzqh=t_xzqh.code (+)
/

Answer 2

/* 报警信息同住人视图 */
CREATE OR REPLACE VIEW V_CH_LIVEPERSON
    (NAME,SEX,ID_NAME_MC,ID_CODE,IN_TIME,OUT_TIME,NATION_MC,BDATE,XZQH_MC,
    ADDRESS,GUEST_ID,NO_ROOM,HOTEL_MC,STA_CODE_MC,BUR_CODE_MC)
AS
SELECT DISTINCT 
NAME,t_sex.called,T_ID_NAME.CALLED,ID_CODE,
   substr(IN_TIME,1,4)||'/'||substr(in_time,5,2)||'/'||substr(in_time,7,2)||' '||substr(in_time,9,2)||':'||substr(in_time,11,2),
   substr(OUT_TIME,1,4)||'/'||substr(out_time,5,2)||'/'||substr(out_time,7,2)||' '||substr(out_time,9,2)||':'||substr(out_time,11,2),
T_NATION.CALLED,BDATE,T_XZQH.CALLED,t_ch_pre.ADDRESS,ID,NO_ROOM,t_hotel.called,t_station.called,t_bureau.called     
FROM 
T_CH_PRE,T_ID_NAME,T_NATION,T_XZQH,t_sex,t_hotel,t_station,t_bureau  
WHERE 
ID_NAME=T_ID_NAME.CODE (+) AND 
NATION=T_NATION.CODE (+) AND 
   XZQH=T_XZQH.CODE (+) and 
sex=t_sex.code (+) and 
substr(id,1,10)=t_hotel.code (+) and 
   t_ch_pre.sta_code=t_station.code (+) and 
t_ch_pre.bur_code=t_bureau.code (+) 
UNION
SELECT DISTINCT 
NAME,t_sex.called,T_ID_NAME.CALLED,ID_CODE,
   substr(IN_TIME,1,4)||'/'||substr(in_time,5,2)||'/'||substr(in_time,7,2)||' '||substr(in_time,9,2)||':'||substr(in_time,11,2),
   substr(OUT_TIME,1,4)||'/'||substr(out_time,5,2)||'/'||substr(out_time,7,2)||' '||substr(out_time,9,2)||':'||substr(out_time,11,2),
T_NATION.CALLED,BDATE,T_XZQH.CALLED,t_ch_rec.ADDRESS,ID,NO_ROOM,t_hotel.called,t_station.called,t_bureau.called     
FROM 
T_CH_REC,T_ID_NAME,T_NATION,T_XZQH,t_sex,t_hotel,t_station,t_bureau   
WHERE 
ID_NAME=T_ID_NAME.CODE (+) AND NATION=T_NATION.CODE (+) AND 
XZQH=T_XZQH.CODE (+) and sex=t_sex.code (+) and substr(id,1,10)=t_hotel.code (+) and 
   t_ch_rec.sta_code=t_station.code (+) and t_ch_rec.bur_code=t_bureau.code (+) 
union 
SELECT DISTINCT 
NAME,t_sex.called,T_ID_NAME.CALLED,ID_CODE,
   substr(IN_TIME,1,4)||'/'||substr(in_time,5,2)||'/'||substr(in_time,7,2)||' '||substr(in_time,9,2)||':'||substr(in_time,11,2),
substr(OUT_TIME,1,4)||'/'||substr(out_time,5,2)||'/'||substr(out_time,7,2)||' '||substr(out_time,9,2)||':'||substr(out_time,11,2),
T_NATION.CALLED,BDATE,T_XZQH.CALLED,t_ch_his.ADDRESS,ID,NO_ROOM,t_hotel.called,t_station.called,t_bureau.called     
FROM 
T_CH_his,T_ID_NAME,T_NATION,T_XZQH,t_sex,t_hotel,t_station,t_bureau   
WHERE 
ID_NAME=T_ID_NAME.CODE (+) AND NATION=T_NATION.CODE (+) AND 
   XZQH=T_XZQH.CODE (+) and sex=t_sex.code (+) and substr(id,1,10)=t_hotel.code (+) and 
   t_ch_his.sta_code=t_station.code (+) and t_ch_his.bur_code=t_bureau.code (+)
/

Answer 3

创建复杂视图要符出对应的代价，不是经常调用，哪写sql实现吧