ORACLE
需求:查询一个时间段内指定小时是00,04,08,12,16,20这些时间点的数据。
SELECT
B.*
FROM
(
SELECT
REGEXP_SUBSTR( '00,04,08,12,16,20', '[^,]+', 1, LEVEL, 'i' ) AS HH
FROM
DUAL CONNECT BY LEVEL <= LENGTH( '00,04,08,12,16,20' ) - LENGTH(
REGEXP_REPLACE( '00,04,08,12,16,20', ',', '' )) + 1
) A
LEFT JOIN (
SELECT
BH.*,
TO_CHAR( C_TIME, 'HH24' ) AS HH
FROM
DATAAUDIT.T_DATA_AUDIT BH
WHERE
C_TIME BETWEEN TO_DATE( '2018-08-01', 'YYYY-MM-DD' )
AND TO_DATE( '2018-08-03', 'YYYY-MM-DD' )
AND STATION_MN = 'A430100_2007'
) B ON A.HH = B.HH