sql 实例1

  SQLTmpData_3G="SELECT VV.SYSTEMTIME,VV.SITE_PRIORITY_NAME AS SITE_PRIORITY,NVL(VV.SITENUM, 0) AS SITE_NUM,(NVL(VV.OUTAGETIME, 0)) AS OUTAGE_TIME,"
                    +" DECODE(NVL(VV.SITENUM, 0),0,0,(NVL(VV.SITENUM, 0) * 24 * 60 - (NVL(VV.OUTAGETIME, 0)))/(NVL(VV.SITENUM, 0) * 24 * 60)) AS AVAILABILITY"
                    +" FROM (SELECT to_char(TRUNC(SYSDATE) - 1,'yyyy-mm-dd') AS SYSTEMTIME,R.SITE_PRIORITY_NAME,"
                    +" (SELECT COUNT(*)FROM T_SITE T, T_DEVICE D WHERE T.SITE_ID = D.SITE_ID"
                    +" AND T.SITE_PRIORITY_ID = R.SITE_PRIORITY_ID AND D.NE_TYPE_ID IN (SELECT TN.NE_TYPE_ID FROM T_NE_TYPE TN"
                    +" WHERE TN.DEV_TYPE_ID = 4) AND T.SITE_STATE_ID = 4) SITENUM,"
                    +" (SELECT SUM(CASE WHEN TRUNC(T.CLEARTIME) <> TO_DATE('1970-01-01', 'yyyy-MM-dd') AND"
                    +" TRUNC(SYSDATE) - 1 = TRUNC(T.CLEARTIME) AND TRUNC(T.FIRSTOCCURRENCE) < TRUNC(SYSDATE) - 1 THEN"
                    +" (T.CLEARTIME - (TRUNC(SYSDATE) - 1)) * 24 * 60 WHEN TRUNC(T.CLEARTIME) = TRUNC(TO_DATE('1970-01-01', 'yyyy-MM-dd')) AND"
                    +" TRUNC(T.FIRSTOCCURRENCE) = TRUNC(SYSDATE) - 1 THEN (TRUNC(SYSDATE) - T.FIRSTOCCURRENCE) * 24 * 60"
                    +" WHEN TRUNC(T.CLEARTIME) <> TO_DATE('1970-01-01', 'yyyy-MM-dd') AND SYSDATE > T.CLEARTIME AND"
                    +" TRUNC(T.FIRSTOCCURRENCE) = TRUNC(SYSDATE) - 1 THEN (T.CLEARTIME - T.FIRSTOCCURRENCE) * 24 * 60"
                    +" WHEN TRUNC(T.CLEARTIME) = TRUNC(TO_DATE('1970-01-01', 'yyyy-MM-dd')) AND TRUNC(T.FIRSTOCCURRENCE) < TRUNC(SYSDATE) - 1 THEN"
                    +" 24 * 60 ELSE 0 END) AS SUMTIME FROM REPORTER_STATUS_H T, T_SITE TS"
                    +" WHERE T.EMSTYPE = 1 AND TS.SITE_PRIORITY_ID = R.SITE_PRIORITY_ID AND T.SITENAME = TS.SITE_NAME"
                    +" AND TS.SITE_STATE_ID = 4 AND T.ALARMID IN ("+NodeBUnavailableGroup+")"
                    +" AND T.DEVICETYPE = 'NodeB') OUTAGETIME FROM T_SITE_PRIORITY_BASE R ORDER BY R.SITE_PRIORITY_ID) VV";