|
|
楼主 |
发表于 2008-7-16 17:46:09
|
显示全部楼层
SELECT t1.ship_no, t1.ship_nam, t1.jing_tim, t1.lig_tim, t1.sum_top,
t1.pkg_kind_cod, t1.cargo_wgt, t1.i_e_id, t1.cargo_kind_cod,
t1.cargo_kind_nam, t2.stop_hour stop_hour,
t2.stop_hour / (t1.cargo_wgt) * 1000 stop1000
FROM (SELECT a.ship_no, MAX (a.ship_nam) ship_nam,
MAX (a.jing_tim) jing_tim, MAX (a.lig_tim) lig_tim,
MAX (a.sum_top) sum_top, a.pkg_kind_cod,
SUM (a.cargo_wgt) cargo_wgt, a.i_e_id, a.cargo_kind_cod,
MAX (a.cargo_kind_nam) cargo_kind_nam
FROM (SELECT tt2.ship_no, MAX (tt2.ship_nam) ship_nam,
MAX (tt2.jing_tim) jing_tim,
MAX (tt2.lig_tim) lig_tim,
MAX (tt2.sum_top) sum_top,
MAX (tt2.pkg_kind_cod) pkg_kind_cod,
SUM (tt2.cargo_wgt) cargo_wgt, tt2.i_e_id,
MAX (tt2.cargo_kind_cod) cargo_kind_cod,
MAX (tt2.cargo_kind_nam) cargo_kind_nam
FROM (SELECT tt1.ship_no, tt1.ship_nam, tt1.jing_tim,
tt1.lig_tim, tt1.sum_top,
FIRST_VALUE (tt1.pkg_kind_cod) OVER (PARTITION BY tt1.ship_no, tt1.i_e_id ORDER BY tt1.cargo_wgt DESC)
pkg_kind_cod,
tt1.cargo_wgt, tt1.i_e_id,
FIRST_VALUE
(tt1.cargo_kind_cod) OVER (PARTITION BY tt1.ship_no, tt1.i_e_id ORDER BY tt1.cargo_wgt DESC)
cargo_kind_cod,
FIRST_VALUE
(tt1.cargo_kind_nam) OVER (PARTITION BY tt1.ship_no, tt1.i_e_id ORDER BY tt1.cargo_wgt DESC)
cargo_kind_nam
FROM (SELECT a.ship_no,
MAX (a.ship_nam) ship_nam,
MAX (NVL (d.rta, d.rtb))
jing_tim,
MAX (d.rtd) lig_tim,
( MAX (d.rtd)
- MAX (NVL (d.rta, d.rtb))
)
* 24 sum_top,
DECODE
(a.pkg_kind_cod,
'0', '0',
'1'
) pkg_kind_cod,
SUM (a.cargo_wgt) cargo_wgt,
a.i_e_id, b.cargo_kind_cod,
MAX
(e.cargo_kind_nam
) cargo_kind_nam
FROM ship d,
ship_thruput a,
c_cargo_list_dept b,
c_cargo_kind_dept e
WHERE d.ship_no = a.ship_no
AND a.cargo_cod = b.cargo_cod
AND b.dept_cod = 'MS_02'
AND b.cargo_kind_cod =
e.cargo_kind_cod
AND e.dept_cod = 'MS_02'
AND b.cargo_kind_cod <> '0114'
AND a.cargo_wgt > 0
GROUP BY a.ship_no,
b.cargo_kind_cod,
a.pkg_kind_cod,
a.i_e_id) tt1) tt2
GROUP BY tt2.ship_no, tt2.i_e_id) a
GROUP BY a.ship_no, a.pkg_kind_cod, a.i_e_id, a.cargo_kind_cod) t1,
(SELECT a.ship_no, SUM ((a.end_tim - a.beg_tim) * 24) stop_hour,
'1' I_E_ID, ======(在此如果'1'改为'I' 就出错)
FROM ship f,
ship_status a,
c_ship_stat b,
(SELECT a.ship_no, MIN (b.beg_tim) end_tim
FROM ship a, v_ship_status b
WHERE a.ship_no = b.ship_no
AND b.ship_stat_cod = '+'
AND a.special_id = '0'
GROUP BY a.ship_no) t1
WHERE f.ship_no = a.ship_no
AND a.ship_stat_cod = b.ship_stat_cod
AND (b.ship_fail_cod LIKE '1%' OR b.ship_fail_cod LIKE '21%')
AND f.ship_no = t1.ship_no(+)
AND f.imp_cargo_cod IS NOT NULL
GROUP BY a.ship_no
UNION ALL
SELECT a.ship_no, SUM ((a.end_tim - a.beg_tim) * 24) stop_hour,
'0' I_E_ID, ======(在此如果'0'改为'E' 就出错)
FROM ship f,
v_ship_status a,
c_ship_stat b,
(SELECT a.ship_no, MAX (b.end_tim) beg_tim
FROM ship a, v_ship_status b
WHERE a.ship_no = b.ship_no
AND b.ship_stat_cod = '-'
AND a.special_id = '0'
GROUP BY a.ship_no) t1
WHERE f.ship_no = a.ship_no
AND a.ship_stat_cod = b.ship_stat_cod
AND (b.ship_fail_cod LIKE '1%' OR b.ship_fail_cod LIKE '21%')
AND f.ship_no = t1.ship_no(+)
AND f.exp_cargo_cod IS NOT NULL
GROUP BY a.ship_no) t2
WHERE t1.ship_no = t2.ship_no AND t1.i_e_id = t2.i_e_id |
|
发表于 2008-7-15 17:14:16
