SELECT

R. ID,

R.LKBH,

R.CDBH,

R.FXBH,

R.ZJBH,

R.SBBH,

R.CPHM,

R.CPYSBH,

R.CPYS,

R.CSYSBH,

R.CSYS,

R.CLWX AS CLWXBH,

R.CLPP AS CLPPBH,

R.CLLX,

R.CLSD,

R.WFDM,

R.HDKQSJ,

R.HDJSSJ,

R.TPSL,

TO_CHAR (

R.TXSJ1,

'YYYY-MM-DD HH24:MI:SS'

) || '.' || R.I_TXSJMS1 AS TXSJ1,

R.TXSJ2,

R.TXSJ3,

R.TXSJ4,

R.TXMC1,

R.TXMC2,

R.TXMC3,

R.TXMC4,

R.TXCRC1,

R.TXCRC2,

R.TXCRC3,

R.TXCRC4,

R.S_TXMC5,

R.S_TXMC6,

R.S_TXMC7,

R.S_TXMC8,

R.I_TXCRC5,

R.I_TXCRC6,

R.I_TXCRC7,

R.I_TXCRC8,

R.TPCCLJ,

R.BJLX,

R.CLBJ,

R.BJR,

R.S_FACE_FILENAME,

R.I_CLLX,

TO_CHAR (

R.BJSJ,

'YYYY-MM-DD HH24:MI:SS'

) AS BJSJ,

R.TPZYLJ,

R.SHR,

R.SHSJ,

R.GGR,

R.GGSJ,

R.DYR,

R.DYSJ,

R.SCR,

R.SCSJ,

R.HCR,

R.HCSJ,

R.FKR,

R.FKSJ,

R.FKJE,

R.WFSCZT,

R.WFSCZTFK,

R.WFSCCFCS,

R.SCQZT,

R.BYZD1,

R.BYZD2,

R.BYZD3,

R.BYZD4,

R.BYZD5,

R.BYZD6,

R.BYZD7,

R.BYZD8,

R.BYZD9,

R.BYZD10,

R.BYZD11,

R.BYZD12,

R.WFBZ,

CR.DWBH,

CR.LKWZ,

CR.LKWD,

CR.LKJD,

CR.BS,

CI.CDMC,

CI.DCZDSD,

CI.DCZXSD,

CI.XCZDSD,

CI.XCZXSD,

CD.S_CAPTION AS SM,

AT .C_ALARM,

AT .VC_DESCRIPTION,

AT .I_LEVEL,

AT .VC_SOUND_FILE,

AT .VC_IMAGE_FILE,

HP.HPZL,

CW.WFXW,

CW.YJ,

CW.JFZ,

CW.FKE1,

CW.FKE2,

CW.XZCF,

CW.QTCS,

CW.XSBJ,

CLPP.BZ AS CLPP,

CLWX.BZ AS CLWX,

BJR_USER.XM AS BJR_XM,

SHR_USER.XM AS SHR_XM,

R.DT_TXSJ1DAY,

R.S_QYID,

R.I_ISUPLOAD,

R.S_PLATEPOS,

R.S_PLATENAME,

R.ZXID,

R.I_TXSJMS1,

R.I_TXSJMS2,

R.I_TXSJMS3,

R.I_TXSJMS4,

R.DJSCZT,

R.DJSCZTFK

FROM

(

SELECT

*

FROM

(

SELECT

pagetable.*, ROWNUM AS rowcounter

FROM

(

SELECT

/*+ INDEX_DESC(ALARM I_TXSJ1ALQY )*/

ALARM. ID,

ALARM.LKBH,

ALARM.CDBH,

ALARM.FXBH,

ALARM.ZJBH,

ALARM.SBBH,

ALARM.CPHM,

ALARM.CPYSBH,

ALARM.CPYS,

ALARM.CSYSBH,

ALARM.CSYS,

ALARM.CLWX,

ALARM.CLPP,

ALARM.CLLX,

ALARM.CLSD,

ALARM.WFDM,

ALARM.HDKQSJ,

ALARM.HDJSSJ,

ALARM.TPSL,

ALARM.TXSJ1,

ALARM.TXSJ2,

ALARM.TXSJ3,

ALARM.TXSJ4,

ALARM.TXMC1,

ALARM.TXMC2,

ALARM.TXMC3,

ALARM.TXMC4,

ALARM.TXCRC1,

ALARM.TXCRC2,

ALARM.TXCRC3,

ALARM.TXCRC4,

ALARM.TPCCLJ,

ALARM.BJLX,

ALARM.CLBJ,

ALARM.BJR,

REPLACE (ALARM.TPCCLJ, '\', '\\') TPZYLJ,

ALARM.BJSJ,

ALARM.SHR,

ALARM.SHSJ,

ALARM.GGR,

ALARM.GGSJ,

ALARM.DYR,

ALARM.DYSJ,

ALARM.SCR,

ALARM.SCSJ,

ALARM.HCR,

ALARM.HCSJ,

ALARM.FKR,

ALARM.FKSJ,

ALARM.FKJE,

ALARM.WFSCZT,

ALARM.WFSCZTFK,

ALARM.WFSCCFCS,

ALARM.BJSK,

ALARM.BJCLBJ,

ALARM.SCQZT,

ALARM.BYZD1,

ALARM.BYZD2,

ALARM.BYZD3,

ALARM.BYZD4,

ALARM.BYZD5,

ALARM.BYZD6,

ALARM.BYZD7,

ALARM.BYZD8,

ALARM.BYZD9,

ALARM.BYZD10,

ALARM.BYZD11,

ALARM.BYZD12,

ALARM.WFBZ,

ALARM.DT_TXSJ1DAY,

ALARM.S_QYID,

ALARM.I_ISUPLOAD,

ALARM.S_PLATEPOS,

ALARM.S_PLATENAME,

ALARM.ZXID,

ALARM.I_TXSJMS1,

ALARM.I_TXSJMS2,

ALARM.I_TXSJMS3,

ALARM.I_TXSJMS4,

ALARM.DJSCZT,

ALARM.DJSCZTFK,

ALARM.S_REC_FILENAME,

ALARM.S_REC_CMS_ID,

ALARM.S_TXMC5,

ALARM.S_TXMC6,

ALARM.S_TXMC7,

ALARM.S_TXMC8,

ALARM.I_TXCRC5,

ALARM.I_TXCRC6,

ALARM.I_TXCRC7,

ALARM.I_TXCRC8,

ALARM.S_FACE_FILENAME,

ALARM.I_CLLX

FROM

TAB_ITS_CAPLOG_ALARM ALARM

WHERE

1 = 1

AND ALARM.TXSJ1 >= TO_DATE (

'2017-12-07 00:00:00',

'YYYY-MM-DD HH24:MI:SS'

)

AND ALARM.TXSJ1 <= TO_DATE (

'2017-12-08 23:59:59',

'YYYY-MM-DD HH24:MI:SS'

)

AND ALARM.CLBJ = '0'

AND ALARM.BJLX = '0'

AND (

ALARM.S_QYID = 'a25d0725-2657-4668-ac5a-3733be8ed849'

OR ALARM.S_QYID = 'dfe134b5-a482-4006-9dae-eccbbf499820'

OR ALARM.S_QYID = 'd0086472-3299-45e5-853e-5aef88997a5d'

OR ALARM.S_QYID = '18a91541-7850-411a-9214-9b43cf6d8244'

OR ALARM.S_QYID = '93011a75-bc48-4b73-a4de-345aa098fe1d'

OR ALARM.S_QYID = '37e5a017-f778-4d20-9221-1ffcbac34d1e'

OR ALARM.S_QYID = 'b58ab9da-5e69-4417-99cf-f87c38fb1f51'

OR ALARM.S_QYID = '8e4014e3-e41e-4b3c-8689-d9dd3c2203eb'

)

ORDER BY

ALARM.TXSJ1 DESC

) pagetable

WHERE

ROWNUM <= 7

) subt

WHERE

subt.rowcounter > 0

) R

LEFT JOIN (

SELECT

OBJAR.S_CODE AS DWBH,

OBJCR.S_CAPTION AS LKWZ,

OBJCR.F_LATITUDE AS LKWD,

OBJCR.F_LONGITUDE AS LKJD,

CR.BS,

OBJCR.S_CODE AS LKBH

FROM

TAB_CROSS CR,

TAB_OBJ OBJCR,

TAB_OBJ OBJAR

WHERE

CR.S_ID = OBJCR.S_ID

AND OBJCR.I_OBJ_TYPE_ID = 101

AND OBJCR.S_PARENT_ID = OBJAR.S_ID

AND OBJAR.I_OBJ_TYPE_ID = 100

UNION ALL

SELECT

OBJAR.S_CODE AS DWBH,

OBJLD.S_CAPTION AS LKWZ,

OBJLD.F_LATITUDE AS LKWD,

OBJLD.F_LONGITUDE AS LKJD,

NULL AS BS,

OBJLD.S_CODE AS LKBH

FROM

TAB_OBJ OBJLD,

TAB_OBJ OBJAR

WHERE

OBJLD.I_OBJ_TYPE_ID = 309

AND OBJLD.S_PARENT_ID = OBJAR.S_ID

AND OBJAR.I_OBJ_TYPE_ID = 100

) CR ON R.LKBH = CR.LKBH

LEFT JOIN (

SELECT

OBJCI.S_CODE AS CDBH,

OBJCR.S_CODE AS LKBH,

OBJCI.S_CAPTION AS CDMC,

CI1.F_DCZDSD AS DCZDSD,

CI1.F_DCZXSD AS DCZXSD,

CI1.F_XCZDSD AS XCZDSD,

CI1.F_XCZXSD AS XCZXSD

FROM

TAB_LANE CI1,

TAB_OBJ OBJCI,

TAB_OBJ OBJCR

WHERE

CI1.S_ID = OBJCI.S_ID

AND OBJCI.I_OBJ_TYPE_ID = 102

AND OBJCI.S_PARENT_ID = OBJCR.S_ID

) CI ON R.CDBH = CI.CDBH

AND R.LKBH = CI.LKBH

LEFT JOIN TAB_DIRECTION CD ON R.FXBH = CD.I_ID

LEFT JOIN TAB_ITS_ALARM_TYPE AT ON R.BJLX = AT .I_INDEX

LEFT JOIN TAB_ITS_HPZL HP ON R.CLLX = HP.ZLID

LEFT JOIN TAB_ITS_CODEWFDM CW ON R.WFDM = CW.DM

LEFT JOIN TAB_ITS_VLP_COLOR CPYS ON R.CPYSBH = CPYS.CPYSBH

LEFT JOIN TAB_ITS_CLPP CLPP ON R.CLPP = CLPP.CLPP

LEFT JOIN TAB_ITS_CLWX CLWX ON R.CLWX = CLWX.CLWX

LEFT JOIN TAB_ITS_CSYS CSYS ON R.CSYSBH = CSYS.CSYSBH

LEFT JOIN (

SELECT

BJR_USER.S_CAPTION AS XM,

BJR_USER.S_CAPTION AS YHDM

FROM

TAB_OBJ BJR_USER

WHERE

BJR_USER.I_OBJ_TYPE_ID = 13

) BJR_USER ON R.BJR = BJR_USER.YHDM

LEFT JOIN (

SELECT

SHR_USER.S_CAPTION AS XM,

SHR_USER.S_CAPTION AS YHDM

FROM

TAB_OBJ SHR_USER

WHERE

SHR_USER.I_OBJ_TYPE_ID = 13

) SHR_USER ON R.SHR = SHR_USER.YHDM

ORDER BY

R.TXSJ1 DESC,

R. ID