sql - 初学者Oracle SQL Count函数多列

Question

我正在查看带有问题和预期输出的练习工作表。事实证明，最后一个问题对我来说很难理解。任何人都可以请帮忙。

问题

创建一个查询以显示员工总数，在该总数中，
2005 年、2006 年、2007 年和 2008 年雇用的员 worker 数。

预期输出格式

Total 2005 2006 2007 2008
107   29   24   19   11

以下是我尝试通过单独的查询获得结果

TO_CHAR(hire_date,'YYYY')
SELECT COUNT(employee_id) AS "Total"
FROM employees;

SELECT COUNT(employee_id) AS "2005"
FROM employees
WHERE TO_CHAR(hire_date,'YYYY') LIKE '2005' GROUP BY TO_CHAR(hire_date,'YYYY') ;

SELECT COUNT(employee_id) AS "2006"
FROM employees
WHERE TO_CHAR(hire_date,'YYYY') LIKE '2006' GROUP BY TO_CHAR(hire_date,'YYYY') ;

SELECT COUNT(employee_id) AS "2007"
FROM employees
WHERE TO_CHAR(hire_date,'YYYY') LIKE '2007' GROUP BY TO_CHAR(hire_date,'YYYY') ;

SELECT COUNT(employee_id) AS "2008"
FROM employees
WHERE TO_CHAR(hire_date,'YYYY') LIKE '2008' GROUP BY TO_CHAR(hire_date,'YYYY') ;

非常感谢任何将结果作为一个查询生成的帮助。

Answer 1

您正在尝试透视数据，因此您可以使用现有查询但添加 CASE聚合内部的表达式:

SELECT COUNT(employee_id) AS "Total",
    sum(case when TO_CHAR(hire_date,'YYYY') = '2005' then 1 else 0 end) "2005",
    sum(case when TO_CHAR(hire_date,'YYYY') = '2006' then 1 else 0 end) "2006",
    sum(case when TO_CHAR(hire_date,'YYYY') = '2007' then 1 else 0 end) "2007",
    sum(case when TO_CHAR(hire_date,'YYYY') = '2008' then 1 else 0 end) "2008"
FROM employees;

根据您的 Oracle 版本，您或许可以使用 PIVOT功能:

select *
from
(
  select count(*) over() Total,
    TO_CHAR(hire_date,'YYYY') Year
  from employees
) 
pivot
(
    count(Year)
    for Year in ('2005', '2006', '2007', '2008')
) 

见 Demo of both queries