代码之家 › 专栏 › 技术社区 › Andrei Ciobanu

按SQL分组

group-by oracle sql

Andrei Ciobanu · 技术社区 · 14 年前

使用Oracle SQL。

我有张桌子 Employees '其中一个属性' hire_date '.我的任务(书本练习)是写 SELECT 这将显示在1995年、1996年、1997年和1998年有多少雇员被雇用。

类似:

TOTAL     1995     1996     1997     1998
-----------------------------------------
20        4        5        29       2

个人很容易计算每年的员工人数,例如:

SELECT
  COUNT(*),
FROM
  employees e
WHERE
  e.hire_date like '%95'

但当我必须以所需的格式“汇总”数据时,我遇到了困难。有什么建议吗?

6 回复 | 直到 8 年前

A_M 14 年前

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);

hire_date  count_hire_date
1995       10
1996       20
1997       30
TOTAL      60

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;

这有一个明显的缺点,即您需要为每个可能的年份在主select语句中添加一个新的子句。

APC 14 年前

语法不直观。这利用了剪切粘贴编码:

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>

user169600 14 年前

以下是我在MySQL中的做法,不知道这是否也适用于Oracle:

SELECT
 YEAR(hire_date), COUNT(*)
FROM 
 employees
GROUP BY 
 YEAR(hire_date)

Leigh Riffel 14 年前

SELECT NVL(hire_date,'Total'), count(hire_date) 
   FROM Employees GROUP BY rollup(hire_date);

如果需要对数据进行透视,请查看答案。如果您有没有数据的年份,但仍然希望年份显示为零计数,则可以执行以下操作:

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;

这是一些测试数据。

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');

Kara Praveen Prasad 10 年前

下面是我在MS SQL中的方法——在Oracle中类似,但我不想给您提供Oracle代码,因为我通常不会编写它。这只是给你一个基本的骨架。

Select
  Year(e.hire_date),
  Count(1)
From
  employees e
Group By
  Year(e.hire_date)

Aklopper 8 年前

我意识到这是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')