oracle求个sql语句，根据当天每小时进行分组查询，如果没数据默认0。该怎么实现？

Question

sql已准备好，把这两个sql拼成一个。以tool表小时数据为基准，只需要8-16每小时的数据。

如果不需要tool表也可以实现，那样最好，谢谢了

数据表“T_IVR_DETAIL_LOG”

SELECT
    TO_CHAR (CURRENTDT, 'hh24') AS HH,
    COUNT (*) num
FROM
    T_IVR_DETAIL_LOG
WHERE
    TO_CHAR (
        TRUNC (CURRENTDT),
        'yyyy-mm-dd'
    ) = (
        SELECT
            TO_CHAR (SYSDATE, 'yyyy-mm-dd')
        FROM
            DUAL
    )
GROUP BY
    TO_CHAR (CURRENTDT, 'hh24')
ORDER BY
    TO_CHAR (CURRENTDT, 'hh24')

小时表

SELECT
    T.HH,
    T.NUM
FROM
    SYS_TOOL T
ORDER BY T.HH

或者这样。我举个简单的例子。

Answer 1

SELECT
ST,HH,
COUNT(TIDL.*)
FROM
SYS_TOOL ST
LEFT JOIN T_IVR_DETAIL_LOG TIDL
ON ST.HH = TO_CHAR(TIDL.CURRENTDT, 'HH24')
GROUP BY ST.HH
ORDER BY ST.HH;