17,088
社区成员
发帖
与我相关
我的任务
分享
WITH t1
AS (SELECT bb || ':1' rn
FROM ( SELECT LPAD (ROWNUM - 1, 2, 0) bb
FROM DUAL
CONNECT BY LEVEL <= 24)
UNION ALL
SELECT bb || ':2' rn
FROM ( SELECT LPAD (ROWNUM - 1, 2, 0) bb
FROM DUAL
CONNECT BY LEVEL <= 24)),
t2
AS ( SELECT aa, COUNT (*) cnt
FROM (SELECT CASE
WHEN TO_CHAR (timestamp, 'mi') < 30
THEN
TO_CHAR (timestamp, 'hh24') || ':1'
ELSE
TO_CHAR (timestamp, 'hh24') || ':2'
END
aa
FROM dba_audit_session
WHERE timestamp >= TRUNC (SYSDATE))
GROUP BY aa)
SELECT t1.rn, NVL (t2.cnt, 0)
FROM t1, t2
WHERE t1.rn = t2.aa(+)
ORDER BY 1
用oracle审计表写的,其他别名啥的还是随意写的 SELECT aa, COUNT (*)
FROM (SELECT CASE
WHEN TO_CHAR (timestamp, 'mi') < 30
THEN
TO_CHAR (timestamp, 'hh24') || ':1'
ELSE
TO_CHAR (timestamp, 'hh24') || ':2'
END
aa
FROM <table_name>
WHERE timestamp >= TRUNC (SYSDATE))
GROUP BY aa
ORDER BY aa