sql - 显示最后一天每小时创建的行数，未创建行时计数为 0

Question

我有一张如下表。

 date             |name |number

5/11/2016 17:00:50| abc |123

5/11/2016 18:00:05| def |456

5/11/2016 18:15:00 |ghi |789

我必须显示最后一天每小时创建的行数。
并且当在那一小时内没有创建任何行时，它应该显示计数 0。
我有一个不使用 PL/SQL 或多个查询的限制。

我已经尝试过下面的查询，但它的问题是计数 0 的行和计数的行

SELECT TRUNC(SYSDATE ,'HH')  - LEVEL/24 AS dates , 0 as count from dual CONNECT BY LEVEL <= 24 union
  SELECT  TRUNC (date, 'hh') as dates, count(*) as count
  FROM table
  where  date> sysdate -1
  GROUP BY TRUNC (date, 'hh')

.

Date              |Count
04-NOV-16 08.00.00  |0
04-NOV-16 09.00.00  |0
04-NOV-16 10.00.00  |0
04-NOV-16 11.00.00  |0
05-NOV-16 12.00.00  |0
05-NOV-16 01.00.00  |0
05-NOV-16 02.00.00  |0
05-NOV-16 03.00.00  |0
05-NOV-16 04.00.00  |0
05-NOV-16 05.00.00  |0
05-NOV-16 06.00.00  |0
05-NOV-16 07.00.00  |0
05-NOV-16 08.00.00  |0
05-NOV-16 09.00.00  |0
05-NOV-16 10.00.00  |0
05-NOV-16 11.00.00  |0
05-NOV-16 12.00.00  |0
05-NOV-16 01.00.00  |0
05-NOV-16 02.00.00  |0
05-NOV-16 03.00.00  |0
05-NOV-16 04.00.00  |0
05-NOV-16 05.00.00  |0
05-NOV-16 05.00.00  |1
05-NOV-16 06.00.00  |0
05-NOV-16 06.00.00  |2
05-NOV-16 07.00.00  |0

我正在使用 Oracle 11g，我必须在单个查询中显示结果。

Answer 1

你会注意到我没有 ORDER BY hh 这就是为什么输出看起来很奇怪。另外，我现在的位置是 11 月 5 日上午 11 点，所以我将在所有小时内回溯到 0!

第一个 CTE inputs 仅用于测试(它不是解决方案的一部分)。在查询的其余部分，将 输入 替换为您的实际表名，列名也相同(最好不要包括 date 和 number !)

with
     inputs ( dt, name, nbr ) as (
     select to_date('5/11/2016 17:00:50', 'dd/mm/yyyy hh24:mi:ss'), 'abc', 123 from dual 
       union all
     select to_date('5/11/2016 18:00:05', 'dd/mm/yyyy hh24:mi:ss'), 'def', 456 from dual 
       union all
     select to_date('5/11/2016 18:15:00', 'dd/mm/yyyy hh24:mi:ss'), 'ghi', 789 from dual
     ),
-- end test data; solution (SQL query) begins here, but add the word WITH before d (hh)
     d ( hh ) as ( 
       select trunc(sysdate, 'hh') - level/24 from dual connect by level <= 24
     )
select d.hh, coalesce(i.ct, 0) as ct
from   d left outer join (
                      select trunc(dt, 'hh') as hh, count(*) as ct
                      from   inputs
                      where  dt >= sysdate - 1
                      group by trunc(dt, 'hh')                      
                    ) i
         on d.hh = i.hh
;

HH                          CT
------------------- ----------
2016-11-04 06:00:00          0
2016-11-04 21:00:00          0
2016-11-04 19:00:00          0
2016-11-04 23:00:00          0

[............................]

2016-11-04 10:00:00          0
2016-11-04 22:00:00          0

 24 rows selected