oracle 开窗函数使用,merge

select cast(last_operate_tm as timestamp) query_time,
plate_num,
container_no,
waybill_no,
operate_zone_code,
waybill_route_status,
operate_batch_code,
lostime,
warntime,
zone_code,
high_worth,
countnum,
exception_node,
appreciation_service_type,
cast(doubt_miss_tm as timestamp) doubt_miss_tm
from (select count(*) over(partition by nvl(nvl2(plate_num, plate_num, container_no), dbms_random.value), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi') order by nvl2(plate_num, plate_num, container_no), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi')) countnum,
row_number() over(partition by nvl(nvl2(plate_num, plate_num, container_no), dbms_random.value), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi') order by nvl2(plate_num, plate_num, container_no), zone_code, to_char(doubt_miss_tm, 'yyyy-MM-dd hh24:mi')) rw,
last_operate_tm,
plate_num,
container_no,
waybill_no,
operate_zone_code,
waybill_route_status,
operate_batch_code,
round(to_number(sysdate - last_operate_tm) * 1440) as lostime,
round(to_number(sysdate - doubt_miss_tm) * 1440) as warntime,
zone_code,
high_worth,
exception_node,
appreciation_service_type,
doubt_miss_tm
from tt_doubt_miss m, tm_department t
where m.zone_code = t.dept_code
and t.dist_code in ('755')) A
WHERE countnum = RW
ORDER BY LAST_OPERATE_TM ASC


1.like 查询
select * from book b where b.name like '%\_%' escape '\';
2.反索引函数
select * from book b where reverse(b.name) like reverse('%02');
3.rollup 函数

MERGE INTO TT_UNIQUE_TCMSEXP TA
USING (SELECT *
FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY A.AEMS_EXP_ID ORDER BY A.TCMS_CREAT_TM DESC) RN
FROM TT_CONVEYANCE_EXP A
WHERE A.CREATE_TM >= V_EXP_SYNCTM
AND A.CREATE_TM <= V_SYSTM + 1 / (24 * 60))
WHERE RN = 1) TB
ON (TA.AEMS_EXP_ID = TB.AEMS_EXP_ID)
WHEN MATCHED THEN
UPDATE
SET TA.DEPART_TYPE = TB.DEPART_TYPE,
TA.VEHICLE_CODE = TB.VEHICLE_CODE,
TA.EXP_TYPE = TB.EXP_TYPE,
TA.EXP_DESC = TB.EXP_DESC,
TA.EXP_RESULT = TB.EXP_RESULT,
TA.EXP_REMARK = TB.EXP_REMARK,
TA.AGENT_NAME = TB.AGENT_NAME,
TA.MODIFY_TM = V_SYSTM
WHEN NOT MATCHED THEN
INSERT
(AEMS_EXP_ID,
DEPART_TYPE,
VEHICLE_CODE,
LINE_CODE,
CONVEYANCE_NAME,
SEND_BILL_ID,
EXP_TYPE,
EXP_DESC,
EXP_RESULT,
EXCEPTION_CODE,
EXP_REMARK,
EXP_SOURCE,
CREATE_TM,
MODIFY_TM,
AGENT_NAME,
IS_SPECIAL,
SEND_DT,
TCMS_CREAT_TM)
VALUES
(TB.AEMS_EXP_ID,
TB.DEPART_TYPE,
TB.VEHICLE_CODE,
TB.LINE_CODE,
TB.CONVEYANCE_NAME,
TB.SEND_BILL_ID,
TB.EXP_TYPE,
TB.EXP_DESC,
TB.EXP_RESULT,
DECODE(TB.EXP_RESULT,
'航班取消',
'1',
'航班拉货',
'2',
'火车取消',
'3',
'火车拉货',
'4',
'5'),
TB.EXP_REMARK,
TB.EXP_SOURCE,
V_SYSTM,
V_SYSTM,
TB.AGENT_NAME,
TB.IS_SPECIAL,
TB.SEND_DT,
TB.TCMS_CREAT_TM);