组合SQL语句并包含用于时间范围的字符串
组合SQL语句并包含用于时间范围的字符串
提问于 2014-10-28 06:53:30
我希望将以下SQL语句组合起来，以便将所有结果放在一列中，而不是在4个单独的列中：
select count(incnum) as counttenth3 from kincident where (to_char(reportdate,'hh24') between 0 and 2.59 or to_char(reportdate,'hh24') between 21 and 23.59
select count(incnum) as counttenth2 from kincident where to_char(reportdate,'hh24') between 15 and 20.59
select count(incnum) as counttenth1 from kincident where to_char(reportdate,'hh24') between 9 and 14.59
select count(incnum) as counttenth0 from kincident where to_char(reportdate,'hh24') between 3 and 8.59唯一的区别是给出了每个语句的时间范围。因此，我试图将它们合并为一列，并希望第二列包含具有给定字符串的行(而不是数据库中的行)。
例如。
Timing | count of incidents
-----------------------------
morning | 26
afternoon | 35
night | 40回答 4
Stack Overflow用户
回答已采纳
发布于 2014-10-28 07:03:30
类似这样的东西-因为我通常编写MS :)
select
t.time_of_day,
count(t.time_of_day)
from
(
---
select
case to_char(reportdate,'hh24')
when between 15 and 20.59 then 'afternoon'
when between 9 and 14.59 then 'morning'
when between 3 and 8.59 then 'early-morning'
else 'night' --when between 0 and 2.59 or to_char(reportdate,'hh24') between 21 and 23.59
end as time_of_day
from
kincident
) t
group by
t.time_of_day使用CASE语句来分类您的时间范围，我已经将其放入子查询中，但您可以在视图中进行包装；作为外部查询的一部分，我们对时间类别进行分组，然后在外部选择中进行计数。
Stack Overflow用户
发布于 2014-10-28 07:01:00
似乎你想用一个案例陈述来分组：
select
case when to_char(reportdate,'hh24') between 3 and 8.59 then 'morning'
when to_char(reportdate,'hh24') between 9 and 14.59 then 'noon'
when to_char(reportdate,'hh24') between 15 and 20.59 then 'afternoon'
else 'night' end as range
,count(incnum)
from
kincident
group by
case when to_char(reportdate,'hh24') between 3 and 8.59 then 'morning'
when to_char(reportdate,'hh24') between 9 and 14.59 then 'noon'
when to_char(reportdate,'hh24') between 15 and 20.59 then 'afternoon'
else 'night' endStack Overflow用户
发布于 2014-10-28 07:20:31
我会重做这个操作，以使用要对其进行连接的时间范围查找表。添加一个包含24行的TIME_CATEGORY表，将每小时映射到“时间类别”。为什么？它通过系统/查询的其他部分变得可重用，并且还允许您实现一个基于时间的规则引擎，并从一个地方使用中央控制。(一旦你开始写案例陈述，你往往会在任何地方重复自己)。
create table time_category(
hour integer primary key,
category varchar2(20)
) organization index;
-- populate categories
begin
for i in 0 .. 2 loop
insert into time_category values(i, 'Night');
end loop;
for i in 3 .. 8 loop
insert into time_category values(i, 'Morning');
end loop;
for i in 9 .. 14 loop
insert into time_category values(i, 'Noon');
end loop;
for i in 15 .. 20 loop
insert into time_category values(i, 'Afternoon');
end loop;
for i in 21 .. 23 loop
insert into time_category values(i, 'Night');
end loop;
end;
/然后，您的查询变成：
-- Join incident table to time categories by hour of reportdate
select category as Timing, count(1) as "Count of incidents"
from kincident i join time_category tc on extract(hour from i.reportdate) = tc.hour
group by category
;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/26602437
复制相关文章
相似问题
腾讯云开发者
