oracle SQL 分组依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4204492/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Grouping By
提问by Andrei Ciobanu
Using ORACLE SQL.
使用 ORACLE SQL。
I have a table 'Employees' with one of the attributes 'hire_date' . My task (book exercise) is to write a SELECTthat will show me how many employees were hired in 1995, 1996, 1997 and 1998 .
我有一个表 ' Employees'，其中一个属性是 ' hire_date'。我的任务（书本练习）是写一份报告SELECT，显示 1995 年、1996 年、1997 年和 1998 年雇佣了多少员工。
Something like:
就像是：
TOTAL 1995 1996 1997 1998
-----------------------------------------
20 4 5 29 2
Individually is easy to count the number of employees for every year, eg:
单独计算每年的员工人数很容易，例如：
SELECT
COUNT(*),
FROM
employees e
WHERE
e.hire_date like '%95'
But I am having difficulties when I have to 'aggregate' the data in the needed format . Any suggestions ?
但是当我必须以所需的格式“聚合”数据时，我遇到了困难。有什么建议 ？
回答by A_M
I'm assuming your hire_date is a varchar2, since you are doing a "like" clause in your example.
我假设你的hiring_date 是一个varchar2，因为你在你的例子中做了一个“like”子句。
Will a simple table with one row per year do?
一张每年只有一行的简单表格可以吗？
If so, try this in Oracle:
如果是这样，请在 Oracle 中尝试此操作：
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);
That should give something like:
那应该给出类似的东西：
hire_date count_hire_date
1995 10
1996 20
1997 30
TOTAL 60
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:
如果您确实需要将结果转换为您在问题中显示的内容，那么如果您在运行查询之前知道不同的年份集合，则可以执行以下操作。因此，例如，如果您知道您的表中只有 1995、1996 和 1997，那么您可以使用以下方法对结果进行透视：
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;
This has the obvious disadvantage of you needing to add a new clause into the main select statement for each possible year.
这有一个明显的缺点，即您需要为每个可能的年份在主选择语句中添加一个新子句。
回答by APC
The syntax is not intuitive. This leverages cut'n'paste coding:
语法不直观。这利用了剪切粘贴编码：
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>
回答by Leigh Riffel
SELECT NVL(hire_date,'Total'), count(hire_date)
FROM Employees GROUP BY rollup(hire_date);
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:
如果您需要 PIVOT 数据，请参阅 A_M 的答案。如果您有没有数据的年份，但仍希望年份以零计数显示，您可以执行以下操作：
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<=9
) b ON a.Year = b.Year;
Here is some test data.
下面是一些测试数据。
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');
回答by Brandon Montgomery
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.
下面是我在 MS SQL 中的做法——它在 Oracle 中是类似的，但我不想尝试给你 Oracle 代码，因为我通常不编写它。这只是为了给你一个基本的骨架。
Select
Year(e.hire_date),
Count(1)
From
employees e
Group By
Year(e.hire_date)
回答by Brandon Montgomery
Here's how I'd do it in MySQL, don't know if this applies to Oracle too:
这是我在 MySQL 中的做法，不知道这是否也适用于 Oracle：
SELECT
YEAR(hire_date), COUNT(*)
FROM
employees
GROUP BY
YEAR(hire_date)
回答by Aklopper
I realize this is 6 years ago, but I also found another unique way of doing this using the DECODE function in Oracle.
我意识到这是 6 年前的事了，但我还发现了另一种使用 Oracle 中的 DECODE 函数的独特方法。
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')
