www.pudn.com > startprepare.rar > KS_LED.sql

/*==============================================================*/ 
/* DBMS name:      ORACLE Version 9i                            */ 
/* Created on:     2007-8-27 15:22:26                           */ 
/*==============================================================*/ 
 
 
drop view V_LED_ZYJQ; 
 
drop view V_LED_LQJQ; 
 
drop view V_LED_JRJQ; 
 
drop view V_LED_JQFK; 
 
drop view V_LED_GG; 
 
drop view V_LED_DQJQ; 
 
/*==============================================================*/ 
/* View: V_LED_DQJQ                                             */ 
/*==============================================================*/ 
create or replace view V_LED_DQJQ as 
SELECT 
	ROWNUM AS XH, XQDW, SLSJ, SJLX, CZZT 
FROM 
( 
	SELECT  
		T_ZZ_DSRXX.DSRMC AS XQDW, 
		T_SJ_SJXX.SLSJ AS SLSJ, 
		T_SJ_DM_SJLX.MS AS SJLX, 
        T_SJ_SJXX.SJCZZT AS CZZT 
	FROM T_SJ_SJXX 
		INNER JOIN T_ZZ_DSRXX ON T_ZZ_DSRXX.DSRBH = T_SJ_SJXX.ZGJG 
		INNER JOIN T_SJ_DM_SJLX ON T_SJ_DM_SJLX.SJLX = T_SJ_SJXX.SJLX 
	WHERE 
		TO_CHAR(SYSDATE,'YYYY-MM-DD') = TO_CHAR(T_SJ_SJXX.SLSJ,'YYYY-MM-DD') 
		AND T_SJ_SJXX.FKSJ IS NULL 
        AND T_SJ_SJXX.SJXZ = 1 
        AND T_SJ_SJXX.SJHBBH IS NULL 
	ORDER BY T_SJ_SJXX.SLSJ DESC 
); 
 
comment on table V_LED_DQJQ is 
'当前警情'; 
 
/*==============================================================*/ 
/* View: V_LED_GG                                               */ 
/*==============================================================*/ 
create or replace view V_LED_GG as 
SELECT 
	  ROWNUM AS XH, 
	  T_XZ_DM_GGLB.MS AS GGLB, 
	  T_XZ_GGXX.GGNR AS GGNR 
FROM T_XZ_GGXX 
	 LEFT JOIN T_XZ_DM_GGLB ON T_XZ_DM_GGLB.GGLB = T_XZ_GGXX.GGLB 
WHERE 
	 (SYSDATE-T_XZ_GGXX.FSSJ)*24 < NVL( 
		(SELECT PZZ FROM T_XT_XTQJPZXX,T_XT_XTPZX WHERE T_XT_XTQJPZXX.XTPZX=T_XT_XTPZX.XTPZX AND T_XT_XTPZX.BSF='SJLBSJJG'), 
		(SELECT QSZ FROM T_XT_XTPZX WHERE T_XT_XTPZX.BSF='SJLBSJJG')); 
 
comment on table V_LED_GG is 
'公告'; 
 
/*==============================================================*/ 
/* View: V_LED_JQFK                                             */ 
/*==============================================================*/ 
create or replace view V_LED_JQFK as 
SELECT 
	ROWNUM AS XH, CJDW, SLSJ, SJLX, CZZT, ZYQJ 
FROM 
( 
	SELECT  
		T_ZZ_DSRXX.DSRMC AS CJDW, 
		T_SJ_SJXX.SLSJ AS SLSJ, 
		T_SJ_DM_SJLX.MS AS SJLX, 
        T_SJ_SJXX.SJCZZT AS CZZT, 
        T_SJ_SJXX.ZYQJ AS ZYQJ 
	FROM T_SJ_SJXX 
		INNER JOIN T_ZZ_DSRXX ON T_ZZ_DSRXX.DSRBH = T_SJ_SJXX.ZGJG 
		INNER JOIN T_SJ_DM_SJLX ON T_SJ_DM_SJLX.SJLX = T_SJ_SJXX.SJLX 
	WHERE 
		TO_CHAR(SYSDATE,'YYYY-MM-DD') = TO_CHAR(T_SJ_SJXX.SLSJ,'YYYY-MM-DD') 
		AND T_SJ_SJXX.FKSJ IS NULL 
        AND T_SJ_SJXX.SJXZ = 1 
        AND T_SJ_SJXX.SJHBBH IS NULL 
        AND EXISTS ( SELECT 1 FROM T_ZZ_JGYJGNXX  
         	WHERE T_ZZ_JGYJGNXX.YJJGBH = T_ZZ_DSRXX.DSRBH AND T_ZZ_JGYJGNXX.YJZN = 1) 
		AND (T_SJ_SJXX.FKSJ IS NULL OR T_SJ_SJXX.FKSJ = TO_DATE('0001-1-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) 
        AND (SYSDATE - T_SJ_SJXX.SLSJ)*24 > 2 
	ORDER BY T_SJ_SJXX.SLSJ DESC 
 ); 
 
comment on table V_LED_JQFK is 
'警情反馈'; 
 
/*==============================================================*/ 
/* View: V_LED_JRJQ                                             */ 
/*==============================================================*/ 
create or replace view V_LED_JRJQ as 
SELECT  
	NVL(S1.JQSL,0) AS JQSL,S2.BJTFHM AS BJFS 
FROM 
( 
	SELECT  
		T_SJ_DHBJXX.BJHM, 
		COUNT(*) AS JQSL 
	FROM T_SJ_BJSLD 
		INNER JOIN T_SJ_DHBJXX ON T_SJ_DHBJXX.BJBH = T_SJ_BJSLD.BJBH 
	WHERE 
	 	TO_CHAR(SYSDATE,'YYYY-MM-DD') = TO_CHAR(T_SJ_BJSLD.BJKSSJ,'YYYY-MM-DD') 
	 	AND T_SJ_BJSLD.BJCLJG = 2 
	GROUP BY T_SJ_DHBJXX.BJHM 
)S1 
RIGHT JOIN  
( 
	SELECT BJTFHM FROM T_SJ_BJHMSZ 
)S2 
ON S1.BJHM = S2.BJTFHM  
ORDER BY S2.BJTFHM; 
 
comment on table V_LED_JRJQ is 
'今日警情'; 
 
/*==============================================================*/ 
/* View: V_LED_LQJQ                                             */ 
/*==============================================================*/ 
create or replace view V_LED_LQJQ as 
SELECT  
	T_ZZ_DSRXX.DSRMC AS XQDW, 
	T_SJ_SJXX.SLSJ AS SLSJ, 
	T_SJ_SJXX.SFDZ AS SFDZ, 
	T_SJ_DM_SJLX.MS AS SJLX, 
    T_SJ_SJXX.SJCZZT AS CZZT 
FROM T_SJ_SJXX 
	INNER JOIN T_ZZ_DSRXX ON T_ZZ_DSRXX.DSRBH = T_SJ_SJXX.ZGJG 
	INNER JOIN T_SJ_DM_SJLX ON T_SJ_DM_SJLX.SJLX = T_SJ_SJXX.SJLX 
WHERE 
	 TO_CHAR(SYSDATE,'YYYY-MM-DD') = TO_CHAR(T_SJ_SJXX.SLSJ,'YYYY-MM-DD') 
	 AND TJ_SJLX_FL(TJ_SJLX_FL(T_SJ_SJXX.SJLX)) IN (63,64) 
	 AND T_SJ_SJXX.FKSJ IS NULL 
     AND T_SJ_SJXX.SJXZ = 1 
     AND T_SJ_SJXX.SJHBBH IS NULL 
ORDER BY T_SJ_SJXX.SLSJ DESC; 
 
comment on table V_LED_LQJQ is 
'两抢警情'; 
 
/*==============================================================*/ 
/* View: V_LED_ZYJQ                                             */ 
/*==============================================================*/ 
create or replace view V_LED_ZYJQ as 
SELECT 
	ROWNUM AS XH, XQDW, SLSJ, SJLX, CZZT 
FROM 
( 
	SELECT  
		T_ZZ_DSRXX.DSRMC AS XQDW, 
		T_SJ_SJXX.SLSJ AS SLSJ, 
		T_SJ_DM_SJLX.MS AS SJLX, 
        T_SJ_SJXX.SJCZZT AS CZZT 
	FROM T_SJ_SJXX 
		INNER JOIN T_ZZ_DSRXX ON T_ZZ_DSRXX.DSRBH = T_SJ_SJXX.ZGJG 
		INNER JOIN T_SJ_DM_SJLX ON T_SJ_DM_SJLX.SJLX = T_SJ_SJXX.SJLX 
	WHERE 
		TO_CHAR(SYSDATE,'YYYY-MM-DD') = TO_CHAR(T_SJ_SJXX.SLSJ,'YYYY-MM-DD') 
        AND T_SJ_SJXX.SJLX IN  
            (SELECT SJLX FROM T_SJ_DM_SJLX WHERE SCBZ = 0 AND SFZY = 1) 
		AND T_SJ_SJXX.FKSJ IS NULL 
        AND T_SJ_SJXX.SJXZ = 1 
        AND T_SJ_SJXX.SJHBBH IS NULL 
	ORDER BY T_SJ_SJXX.SLSJ DESC 
); 
 
comment on table V_LED_ZYJQ is 
'重要警情';