blocks|key|1351063|text|下面是我如何在MS+SQL中做这件事--它在Oracle中将是类似的，但我不想尝试给你Oracle代码，因为我通常不会写它。这只是为了给你一个基本的框架。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1351064|Select
++Year(e.hire_date),
++Count(1)
From
++employees+e
Group+By
++Year(e.hire_date)|code-block|syntax|javascript|1351065|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|$]]

Here's how I would do it in MS SQL - it will be similar in Oracle, but I don't want to try to give you Oracle code because I don't usually write it. This is just to get you a basic skeleton.

<pre><code>Select
 Year(e.hire_date),
 Count(1)
From
 employees e
Group By
 Year(e.hire_date)
</code></pre>

blocks|key|1349296|text|下面是我如何在MySQL中做到这一点，不知道这是否也适用于甲骨文：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1349297|SELECT
+YEAR(hire_date),+COUNT(*)
FROM+
+employees
GROUP+BY+
+YEAR(hire_date)|code-block|syntax|javascript|1349298|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|$]]

Here's how I'd do it in MySQL, don't know if this applies to Oracle too:

<pre><code>SELECT
 YEAR(hire_date), COUNT(*)
FROM 
 employees
GROUP BY 
 YEAR(hire_date)
</code></pre>

blocks|key|3664188|text|我假设您的hire_date是一个varchar2，因为您在示例中做了一个"like“子句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3664189|一个简单的表每年只有一行行就行了吗？|3664190|如果是这样，请在Oracle中尝试：|3664191|select+case+grouping(hire_date)
++++++++++++when+0+then+hire_date
++++++++++++else+'TOTAL'
+++++++end+hire_date,
+++++++count(hire_date)+as+count_hire_date
+from+employees
+group+by+rollup(hire_date);|code-block|syntax|javascript|3664192|这应该会给出一些类似的东西：|3664193|hire_date++count_hire_date
1995+++++++10
1996+++++++20
1997+++++++30
TOTAL++++++60|3664194|如果您确实需要将结果转换为您在问题中所显示的内容，那么您可以在运行查询之前知道不同的年份集的情况下执行以下操作。因此，例如，如果您知道您的表中只有1995、1996和1997，那么您可以使用以下命令来透视结果：|3664195|SELECT
++MAX(CASE+WHEN+hire_date+=+'TOTAL'++++THEN+ilv.count_hire_date+++END)+total,
++MAX(CASE+WHEN+hire_date+=+'1995'+++++THEN+ilv.count_hire_date+++END)+count_1995,
++MAX(CASE+WHEN+hire_date+=+'1996'+++++THEN+ilv.count_hire_date+++END)+count_1996,
++MAX(CASE+WHEN+hire_date+=+'1997'+++++THEN+ilv.count_hire_date+++END)+count_1997
from+(
++++select+case+grouping(hire_date)
+++++++++++++when+0+then+hire_date
+++++++++++++else+'TOTAL'
+++++++++++end+hire_date,
+++++++++++count(hire_date)+as+count_hire_date
++++from+employees
++++group+by+rollup(hire_date)
++)+ilv;|3664196|这有一个明显的缺点，就是您需要为每个可能的年份在主select语句中添加一个新的子句。|3664197|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|11|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|13|8|@]|9|@]|A|$I|J]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

I'm assuming your hire_date is a varchar2, since you are doing a "like" clause in your example.

Will a simple table with one row per year do?

If so, try this in Oracle:

<pre><code>select case grouping(hire_date)
 when 0 then hire_date
 else 'TOTAL'
 end hire_date,
 count(hire_date) as count_hire_date
 from employees
 group by rollup(hire_date);
</code></pre>

That should give something like:

<pre><code>hire_date count_hire_date
1995 10
1996 20
1997 30
TOTAL 60
</code></pre>

If you do need to pivot your results into something like you've shown in your question, then you can do the following if you know the distinct set of years prior to running the query. So for example, if you knew that you only had 1995, 1996 and 1997 in your table, then you could pivot the results using this:

<pre><code>SELECT
 MAX(CASE WHEN hire_date = 'TOTAL' THEN ilv.count_hire_date END) total,
 MAX(CASE WHEN hire_date = '1995' THEN ilv.count_hire_date END) count_1995,
 MAX(CASE WHEN hire_date = '1996' THEN ilv.count_hire_date END) count_1996,
 MAX(CASE WHEN hire_date = '1997' THEN ilv.count_hire_date END) count_1997
from (
 select case grouping(hire_date)
 when 0 then hire_date
 else 'TOTAL'
 end hire_date,
 count(hire_date) as count_hire_date
 from employees
 group by rollup(hire_date)
 ) ilv;
</code></pre>

This has the obvious disadvantage of you needing to add a new clause into the main select statement for each possible year.

blocks|key|1349351|text|SELECT+NVL(hire_date,'Total'),+count(hire_date)+
+++FROM+Employees+GROUP+BY+rollup(hire_date);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1349352|如果您需要透视数据，请参阅A_M的答案。如果您有几年没有数据，但仍然希望年份显示为零计数，则可以执行以下操作：|unstyled|1349353|SELECT+NVL(a.Year,b.Year),+NVL2(a.Year,a.Count,0)+FROM
(
SELECT+NVL(hire_date,'Total')+Year,+count(hire_date)+Count
+++FROM+Employees+GROUP+BY+rollup(hire_date)
)+a
FULL+JOIN
(
+++SELECT+to_char(2000+%2B+rownum,'FM0000')+Year+FROM+dual+CONNECT+BY+rownum<=9
)+b+ON+a.Year+=+b.Year;|1349354|以下是一些测试数据。|1349355|create+table+Employees+(hire_date+Varchar2(4));

insert+into+Employees+values+('2005');
insert+into+Employees+values+('2004');
insert+into+Employees+values+('2006');
insert+into+Employees+values+('2009');
insert+into+Employees+values+('2009');
insert+into+Employees+values+('2005');
insert+into+Employees+values+('2004');
insert+into+Employees+values+('2006');
insert+into+Employees+values+('2006');
insert+into+Employees+values+('2006');|1349356|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|M|3|-4|5|F|7|T|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>SELECT NVL(hire_date,'Total'), count(hire_date) 
 FROM Employees GROUP BY rollup(hire_date);
</code></pre>

If you need to PIVOT the data see A_M's answer. If you have years for which you have no data, yet still want the year to show up with a zero count you could do something like the following:

<pre><code>SELECT NVL(a.Year,b.Year), NVL2(a.Year,a.Count,0) FROM
(
SELECT NVL(hire_date,'Total') Year, count(hire_date) Count
 FROM Employees GROUP BY rollup(hire_date)
) a
FULL JOIN
(
 SELECT to_char(2000 + rownum,'FM0000') Year FROM dual CONNECT BY rownum&lt;=9
) b ON a.Year = b.Year;
</code></pre>

Here is some test data.

<pre><code>create table Employees (hire_date Varchar2(4));

insert into Employees values ('2005');
insert into Employees values ('2004');
insert into Employees values ('2006');
insert into Employees values ('2009');
insert into Employees values ('2009');
insert into Employees values ('2005');
insert into Employees values ('2004');
insert into Employees values ('2006');
insert into Employees values ('2006');
insert into Employees values ('2006');
</code></pre>

blocks|key|2093675|text|语法不直观。这利用了剪切粘贴代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2093676|SQL>+select
++2++++++sum(case+when+to_char(hiredate,+'YYYY')+=+'1980'+then+1+else+0+end)+as+"1980"
++3++++++,+sum(case+when+to_char(hiredate,+'YYYY')+=+'1981'+then+1+else+0+end)+as+"1981"
++4++++++,+sum(case+when+to_char(hiredate,+'YYYY')+=+'1982'+then+1+else+0+end)+as+"1982"
++5++++++,+sum(case+when+to_char(hiredate,+'YYYY')+=+'1983'+then+1+else+0+end)+as+"1983"
++6++++++,+sum(case+when+to_char(hiredate,+'YYYY')+=+'1987'+then+1+else+0+end)+as+"1987"
++7++++++,+count(*)+as+total
++8++from+emp
++9++/

++++++1980+++++++1981+++++++1982+++++++1983+++++++1987++++++TOTAL
----------+----------+----------+----------+----------+----------
+++++++++1+++++++++10++++++++++1++++++++++0++++++++++2+++++++++20

Elapsed:+00:00:00.00
SQL>|code-block|syntax|javascript|2093677|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|$]]

The syntax is not intuitive. This leverages cut'n'paste coding:

<pre><code>SQL&gt; select
 2 sum(case when to_char(hiredate, 'YYYY') = '1980' then 1 else 0 end) as "1980"
 3 , sum(case when to_char(hiredate, 'YYYY') = '1981' then 1 else 0 end) as "1981"
 4 , sum(case when to_char(hiredate, 'YYYY') = '1982' then 1 else 0 end) as "1982"
 5 , sum(case when to_char(hiredate, 'YYYY') = '1983' then 1 else 0 end) as "1983"
 6 , sum(case when to_char(hiredate, 'YYYY') = '1987' then 1 else 0 end) as "1987"
 7 , count(*) as total
 8 from emp
 9 /

 1980 1981 1982 1983 1987 TOTAL
---------- ---------- ---------- ---------- ---------- ----------
 1 10 1 0 2 20

Elapsed: 00:00:00.00
SQL&gt;
</code></pre>

blocks|key|1349395|text|我知道这是6年前的事了，但我也找到了另一种使用Oracle中的DECODE函数来完成此任务的独特方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1349396|select
count(decode(to_char(hire_date,+'yyyy')+,+'2005',+hire_date,+null))+hired_in_2005,
count(decode(to_char(hire_date,+'yyyy')+,+'2006',+hire_date,+null))+hired_in_2006,
count(decode(to_char(hire_date,+'yyyy')+,+'2007',+hire_date,+null))+hired_in_2007,
count(*)+total_emp
from+employees+
where+to_char(hire_date,'yyyy')+IN+('2005','2006','2007')|code-block|syntax|javascript|1349397|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|$]]

I realize this is 6 years ago, but I also found another unique way of doing this using the DECODE function in Oracle.

<pre><code>select
count(decode(to_char(hire_date, 'yyyy') , '2005', hire_date, null)) hired_in_2005,
count(decode(to_char(hire_date, 'yyyy') , '2006', hire_date, null)) hired_in_2006,
count(decode(to_char(hire_date, 'yyyy') , '2007', hire_date, null)) hired_in_2007,
count(*) total_emp
from employees 
where to_char(hire_date,'yyyy') IN ('2005','2006','2007')
</code></pre>

Using ORACLE SQL.

I have a table '<code>Employees</code>' with one of the attributes '<code>hire_date</code>' . My task (book exercise) is to write a <code>SELECT</code> that will show me how many employees were hired in 1995, 1996, 1997 and 1998 .

Something like:

<pre><code>TOTAL 1995 1996 1997 1998
-----------------------------------------
20 4 5 29 2
</code></pre>

Individually is easy to count the number of employees for every year, eg:

<pre><code>SELECT
 COUNT(*),
FROM
 employees e
WHERE
 e.hire_date like '%95'
</code></pre>

But I am having difficulties when I have to 'aggregate' the data in the needed format .
Any suggestions ?

SQL Grouping By

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

使用ORACLE SQL。我有一个表'Employees‘，其中一个属性是'hire_date’。我的任务(书本练习)是写一个SELECT，告诉我1995年、1996年、1997年和1998年雇佣了多少员工。类似于：TOTAL     1995     1996     1997     1998-----------...

问SQL分组依据
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL分组依据EN