According to a certain date field, quantity statistics of 0 at the same time, the related SQL how to write?

CodePudding user response:

The count (case when num=0 then 1 else null end)

Where trunc (dat, 'dd')=trunc (sysdate, 'dd')

Group by trunc (t1) dat, 'dd') + (floor (to_char (t1) dat, 'SSSSS)/1800) * 1800)/86400

CodePudding user response:

Replace Chinese, with your related table name, field,
WITH T1 AS (
The SELECT LEVEL L, TRUNC (SYSDATE) + (LEVEL 1)/T1, 48 TRUNC (SYSDATE) + (LEVEL)/48-1/86400 T2 FROM DUAL CONNECT BY LEVEL & lt;=48)
SELECT T1. *, SUM (table name. Related fields) FROM T1 LEFT the JOIN table name ON the name of the table. The time field BETWEEN T1, T1 AND T1, T2
GROUP BY T1. L