我正在尝试区分一个select语句。然而,早上7点,早上8点,早上9点等有多种结果。我希望结果显示:
计算时间7点10
6 8am
等。相反,它显示的是所有的7am所有的8am等等。
select
case
when aq.datestarted between to_date( '22-MAY-2014 07:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 07:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '7am'
when aq.datestarted between to_date( '22-MAY-2014 08:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 08:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '8am'
when aq.datestarted between to_date( '22-MAY-2014 09:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 09:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '9am'
when aq.datestarted between to_date( '22-MAY-2014 10:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 10:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '10am'
when aq.datestarted between to_date( '22-MAY-2014 11:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 11:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '11am'
END as Time
from archivedqueue aq
where aq.datestarted between to_date( '22-MAY-2014 07:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 11:59:59', 'dd-mon-yyyy hh24:mi:ss' )
select COUNT(*), Time
FROM
(SELECT
case
when aq.datestarted between to_date( '22-MAY-2014 07:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 07:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '7am'
when aq.datestarted between to_date( '22-MAY-2014 08:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 08:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '8am'
when aq.datestarted between to_date( '22-MAY-2014 09:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 09:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '9am'
when aq.datestarted between to_date( '22-MAY-2014 10:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 10:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '10am'
when aq.datestarted between to_date( '22-MAY-2014 11:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 11:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '11am'
END as Time
from archivedqueue aq
where aq.datestarted between to_date( '22-MAY-2014 07:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 11:59:59', 'dd-mon-yyyy hh24:mi:ss' )) tempTable
GROUP BY Time
Group by Time
将所有的7ams,8ams组合在一起。Count(*)
计算每组的行数
SELECT COUNT(*) AS Count, Time
FROM (SELECT case
when aq.datestarted between to_date( '22-MAY-2014 07:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 07:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '7am'
when aq.datestarted between to_date( '22-MAY-2014 08:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 08:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '8am'
when aq.datestarted between to_date( '22-MAY-2014 09:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 09:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '9am'
when aq.datestarted between to_date( '22-MAY-2014 10:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 10:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '10am'
when aq.datestarted between to_date( '22-MAY-2014 11:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 11:59:59', 'dd-mon-yyyy hh24:mi:ss' ) then '11am'
END as Time
FROM archivedqueue aq
WHERE aq.datestarted between to_date( '22-MAY-2014 07:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 11:59:59', 'dd-mon-yyyy hh24:mi:ss' )
) name_required
GROUP BY Time
ORDER BY Time;
子查询是您编写的(除了在各种BETWEEN条件中的第二个TO_DATE中的修复);当您可以看到没有水平滚动的代码时,更容易发现问题!),稍微重新格式化。注意,ORDER BY是不完美的,因为排序是基于文本的(因此10am
和11am
条目将出现在7am
到9am
条目之前)。如果您不喜欢这样,请使用文本可排序的格式,如07
或0700
或0700-07:59
,而不是'am'标记的时间。
不可扩展。你需要更努力地研究CASE语句中的内容。
也许:
TO_CHAR(aq.datestarted, 'yyyy-mm-dd hh24:00') AS time
WHERE子句中的限制条件然后提供正确的搜索限制。
SELECT COUNT(*) AS Count, TO_CHAR(aq.datestarted, 'yyyy-mm-dd hh:00') AS time
FROM archivedqueue aq
WHERE aq.datestarted BETWEEN to_date('22-MAY-2014 07:00:00', 'dd-mon-yyyy hh24:mi:ss')
AND to_date('22-MAY-2014 11:59:59', 'dd-mon-yyyy hh24:mi:ss')
GROUP BY Time
ORDER BY Time;
要使其按您期望的方式工作,请使用下面的内容…然而,可能有比硬编码日期和时间更好的方法。
SELECT COUNT(*) AS 'Count',
CASE
WHEN aq.datestarted BETWEEN to_date( '22-MAY-2014 07:00:00', 'dd-mon-yyyy hh24:mi:ss' ) AND to_date( '22-MAY-2014 " 07:59:59', 'dd-mon-yyyy hh24:mi:ss' ) THEN '7am'
WHEN aq.datestarted BETWEEN to_date( '22-MAY-2014 08:00:00', 'dd-mon-yyyy hh24:mi:ss' ) AND to_date( '22-MAY-2014 " 08:59:59', 'dd-mon-yyyy hh24:mi:ss' ) THEN '8am'
WHEN aq.datestarted BETWEEN to_date( '22-MAY-2014 09:00:00', 'dd-mon-yyyy hh24:mi:ss' ) AND to_date( '22-MAY-2014 " 09:59:59', 'dd-mon-yyyy hh24:mi:ss' ) THEN '9am'
WHEN aq.datestarted BETWEEN to_date( '22-MAY-2014 10:00:00', 'dd-mon-yyyy hh24:mi:ss' ) AND to_date( '22-MAY-2014 " 10:59:59', 'dd-mon-yyyy hh24:mi:ss' ) THEN '10am'
WHEN aq.datestarted BETWEEN to_date( '22-MAY-2014 11:00:00', 'dd-mon-yyyy hh24:mi:ss' ) AND to_date( '22-MAY-2014 " 11:59:59', 'dd-mon-yyyy hh24:mi:ss' ) THEN '11am'
END AS 'Time'
FROM archivedqueue aq
WHERE aq.datestarted BETWEEN to_date( '22-MAY-2014 07:00:00', 'dd-mon-yyyy hh24:mi:ss'
GROUP BY CASE
WHEN aq.datestarted BETWEEN to_date( '22-MAY-2014 07:00:00', 'dd-mon-yyyy hh24:mi:ss' ) AND to_date( '22-MAY-2014 " 07:59:59', 'dd-mon-yyyy hh24:mi:ss' ) THEN '7am'
WHEN aq.datestarted BETWEEN to_date( '22-MAY-2014 08:00:00', 'dd-mon-yyyy hh24:mi:ss' ) AND to_date( '22-MAY-2014 " 08:59:59', 'dd-mon-yyyy hh24:mi:ss' ) THEN '8am'
WHEN aq.datestarted BETWEEN to_date( '22-MAY-2014 09:00:00', 'dd-mon-yyyy hh24:mi:ss' ) AND to_date( '22-MAY-2014 " 09:59:59', 'dd-mon-yyyy hh24:mi:ss' ) THEN '9am'
WHEN aq.datestarted BETWEEN to_date( '22-MAY-2014 10:00:00', 'dd-mon-yyyy hh24:mi:ss' ) AND to_date( '22-MAY-2014 " 10:59:59', 'dd-mon-yyyy hh24:mi:ss' ) THEN '10am'
WHEN aq.datestarted BETWEEN to_date( '22-MAY-2014 11:00:00', 'dd-mon-yyyy hh24:mi:ss' ) AND to_date( '22-MAY-2014 " 11:59:59', 'dd-mon-yyyy hh24:mi:ss' ) THEN '11am'
END
你只需要这个:
select count(*) count, hour(datestarted) time
from rchivedqueue
where datestarted between to_date( '22-MAY-2014 07:00:00', 'dd-mon-yyyy hh24:mi:ss' ) and to_date( '22-MAY-2014 " 11:59:59', 'dd-mon-yyyy hh24:mi:ss' )
group by hour(datestarted)