按小时优化组查询
按小时优化组查询
提问于 2017-04-25 01:33:35
请帮助优化我的查询。看上去太笨重了。我想在sql中有更好的方法来处理小时(日期时间)。
有一个表dauditnew被填充，填充日期时间存储在auditdate列中。
查询返回按小时计数的行。
SELECT t.Hour, COUNT(t.Hour) as Count FROM dauditnew d,
(SELECT 0 as Hour FROM DUAL UNION
SELECT 1 FROM DUAL UNION
SELECT 2 FROM DUAL UNION
SELECT 3 FROM DUAL UNION
SELECT 4 FROM DUAL UNION
SELECT 5 FROM DUAL UNION
SELECT 6 FROM DUAL UNION
SELECT 7 FROM DUAL UNION
SELECT 8 FROM DUAL UNION
SELECT 9 FROM DUAL UNION
SELECT 10 FROM DUAL UNION
SELECT 11 FROM DUAL UNION
SELECT 12 FROM DUAL UNION
SELECT 13 FROM DUAL UNION
SELECT 14 FROM DUAL UNION
SELECT 15 FROM DUAL UNION
SELECT 16 FROM DUAL UNION
SELECT 17 FROM DUAL UNION
SELECT 18 FROM DUAL UNION
SELECT 19 FROM DUAL UNION
SELECT 20 FROM DUAL UNION
SELECT 21 FROM DUAL UNION
SELECT 22 FROM DUAL UNION
SELECT 23 FROM DUAL) t
where
d.auditdate >= TO_DATE('25.04.2017 ' || t.Hour, 'dd.mm.yyyy HH24') and
d.auditdate <= TO_DATE('25.04.2017 ' || t.Hour || '_59_59', 'dd.mm.yyyy HH24_MI_SS')
group by t.Hour 回答 1
Stack Overflow用户
回答已采纳
发布于 2017-04-25 01:42:25
你想从这样的事情开始：
select trunc(d.auditdate, 'HH24') as hh, count(*)
from dauditnew d
where d.auditdate >= '2017-04-25' and d.auditdate < '2017-04-26'
group by trunc(d.auditdate, 'HH24')
order by hh;如果这错过了几个小时，那么您可以使用left join作为子查询。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43600166
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号