TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
- SET ECHO OFF
- SET FEEDBACK ON
- SET HEADING ON
- SET LINESIZE 180
- SET PAGESIZE 2000
- SET TIMING OFF
- SET TRIMSPOOL ON
- SET VERIFY OFF
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN DAY FORMAT a8 HEADING 'D/T'
- COLUMN H00 FORMAT 999B HEADING '00'
- COLUMN H01 FORMAT 999B HEADING '01'
- COLUMN H02 FORMAT 999B HEADING '02'
- COLUMN H03 FORMAT 999B HEADING '03'
- COLUMN H04 FORMAT 999B HEADING '04'
- COLUMN H05 FORMAT 999B HEADING '05'
- COLUMN H06 FORMAT 999B HEADING '06'
- COLUMN H07 FORMAT 999B HEADING '07'
- COLUMN H08 FORMAT 999B HEADING '08'
- COLUMN H09 FORMAT 999B HEADING '09'
- COLUMN H10 FORMAT 999B HEADING '10'
- COLUMN H11 FORMAT 999B HEADING '11'
- COLUMN H12 FORMAT 999B HEADING '12'
- COLUMN H13 FORMAT 999B HEADING '13'
- COLUMN H14 FORMAT 999B HEADING '14'
- COLUMN H15 FORMAT 999B HEADING '15'
- COLUMN H16 FORMAT 999B HEADING '16'
- COLUMN H17 FORMAT 999B HEADING '17'
- COLUMN H18 FORMAT 999B HEADING '18'
- COLUMN H19 FORMAT 999B HEADING '19'
- COLUMN H20 FORMAT 999B HEADING '20'
- COLUMN H21 FORMAT 999B HEADING '21'
- COLUMN H22 FORMAT 999B HEADING '22'
- COLUMN H23 FORMAT 999B HEADING '23'
- COLUMN TOTAL FORMAT 999,999,999 HEADING 'Total'
- BREAK ON report
- COMPUTE sum label 'total' avg label 'avg' max label 'max' min label 'min' OF total ON report
- SELECT
- SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
- , COUNT(*) TOTAL
- FROM
- v$log_history a
- where first_time>sysdate-30
- GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
- ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
- /
复制代码
|
|