blocks|key|1201385|text|似乎你想用一个案例陈述来分组：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1201386|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'+end|code-block|syntax|javascript|1201387|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Seems you want group by with a case statement:

<pre><code>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' end
</code></pre>

blocks|key|1627892|text|类似这样的东西-因为我通常编写MS+:)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1627893|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|code-block|syntax|javascript|1627894|使用CASE语句来分类您的时间范围，我已经将其放入子查询中，但您可以在视图中进行包装；作为外部查询的一部分，我们对时间类别进行分组，然后在外部选择中进行计数。|1627895|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Something like this - untested since I generally write MS SQL :)

<pre><code>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
</code></pre>

Use a CASE statement to categories your time ranges, which I've put into a sub-query, but you could wrap in a view; as part of the outer query we group the time-of-day categories and count then in the outer select.

blocks|key|1627897|text|作为张荣佳回答的另一种选择:1)要获得常量值，只需使用字符串常量而不是cloumn。2.)您可以使用union将查询连接到一个查询中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1627898|看起来是这样的：|1627899|select+'night',+count(incnum)+as+incidentcout+from+kincident+where+(to_char(reportdate,'hh24')+between+0+and+2.59+or+to_char(reportdate,'hh24')+between+21+and+23.59+
union
select+'afternoon',+count(incnum)+as+incidentcout++from+kincident+where+to_char(reportdate,'hh24')+between+15+and+20.59
union
select+'noon',+count(incnum)+as+incidentcout++as+counttenth1+from+kincident+where+to_char(reportdate,'hh24')+between+9+and+14.59
union
select+'morning',+count(incnum)+as+incidentcout+from+kincident++where+to_char(reportdate,'hh24')+between+3+and+8.59|code-block|syntax|javascript|1627900|entityMap^0|1E|5|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

As an alternative to Jaugar Chang's answer:
1.) To get the constant values just use a string-constant instead of a cloumn name.
2.) you can use <code>union</code> to join the queries into one.

So it'll look like this:

<pre><code>select 'night', count(incnum) as incidentcout from kincident where (to_char(reportdate,'hh24') between 0 and 2.59 or to_char(reportdate,'hh24') between 21 and 23.59 
union
select 'afternoon', count(incnum) as incidentcout from kincident where to_char(reportdate,'hh24') between 15 and 20.59
union
select 'noon', count(incnum) as incidentcout as counttenth1 from kincident where to_char(reportdate,'hh24') between 9 and 14.59
union
select 'morning', count(incnum) as incidentcout from kincident where to_char(reportdate,'hh24') between 3 and 8.59
</code></pre>

blocks|key|1201474|text|我会重做这个操作，以使用要对其进行连接的时间范围查找表。添加一个包含24行的TIME_CATEGORY表，将每小时映射到“时间类别”。为什么？它通过系统/查询的其他部分变得可重用，并且还允许您实现一个基于时间的规则引擎，并从一个地方使用中央控制。(一旦你开始写案例陈述，你往往会在任何地方重复自己)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1201475|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;
/|code-block|syntax|javascript|1201476|然后，您的查询变成：|1201477|--+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
;|1201478|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I'd redo this to use a lookup table of time ranges to join against. Add a TIME_CATEGORY table that has 24 rows, mapping each hour to a "time category". Why? It becomes reusable through other parts of your system / queries, and also allows you to implement a time based rules engine with central control from a single place. (Once you start down the route of writing case statements, you often end up repeating yourself everywhere).

<pre><code>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;
/
</code></pre>

Then your query becomes:

<pre><code>-- 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
;
</code></pre>

I'm wanting to combine the following SQL statements so that I have all the results in one column rather than in 4 seperate ones:

<pre><code>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
</code></pre>

The only difference is the time range given for each statement.
So I'm trying to combine them all into one to be in one column and I want a second column to include rows with given strings (not from the database)

E.g.

<pre><code>Timing | count of incidents
-----------------------------
morning | 26
afternoon | 35
night | 40
</code></pre>

Combining SQL statements and including strings for time ranges

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我希望将以下SQL语句组合起来，以便将所有结果放在一列中，而不是在4个单独的列中：select count(incnum) as counttenth3 from kincident where (to_char(reportdate,'hh24') between 0 and 2.59 or to_char(repo...

问组合SQL语句并包含用于时间范围的字符串
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问组合SQL语句并包含用于时间范围的字符串EN