• 1.7k阅读
• 2021年03月31日
• 0评论
/ / 正文

## 题一：注意count的坑

1、编写一个查询显示员工总数以及其中在2004年、2005年、2006年和2007年聘用的员工数。创建合适的列标题。(注：分别使用横表、纵表)

### 横表

select count(employee_id) as "total",
count(extract(year from hire_date)=2004) as "2004",
count(extract(year from hire_date)=2005) as "2005",
count(extract(year from hire_date)=2006) as "2006",
count(extract(year from hire_date)=2007) as "2007"
from hr.employees;

select count(employee_id) as "total",
extract(year from hire_date)=2004 as "2004",
extract(year from hire_date)=2005 as "2005",
extract(year from hire_date)=2006 as "2006",
extract(year from hire_date)=2007 as "2007"
from hr.employees
group by "2004", "2005", "2006", "2007";

select count(employee_id) as "total",
count((extract(year from hire_date)=2004)=true) as "2004",
count((extract(year from hire_date)=2005)=true) as "2005",
count((extract(year from hire_date)=2006)=true) as "2006",
count((extract(year from hire_date)=2007)=true) as "2007"
from hr.employees;

select count(extract(year from hire_date)) as "total",
count(case when extract(year from hire_date)=2004 then 1 else null end) as "2004",
count(case when extract(year from hire_date)=2005 then 1 else null end) as "2005",
count(case when extract(year from hire_date)=2006 then 1 else null end) as "2006",
count(case when extract(year from hire_date)=2007 then 1 else null end) as "2007"
from hr.employees;

### 纵表

select extract(year from hire_date) as "年份", count(employee_id) as "雇佣员工数"
from hr.employees
where extract(year from hire_date) in (2004, 2005, 2006, 2007)
group by "年份";

select '2004' as "年份", count(*) as "雇佣员工数" from hr.employees where extract(year from hire_date)=2004
UNION ALL
select '2005' as "年份", count(*) as "雇佣员工数" from hr.employees where extract(year from hire_date)=2005
UNION ALL
select '2006' as "年份", count(*) as "雇佣员工数" from hr.employees where extract(year from hire_date)=2006
UNION ALL
select '2007' as "年份", count(*) as "雇佣员工数" from hr.employees where extract(year from hire_date)=2007
UNION ALL
select '总数' as "年份", count(extract(year from hire_date)) as "雇佣员工数" from hr.employees;

## 题二：交叉表

2、编写一个矩阵查询显示department_id为20、50、80和90的职务(job_id)、基于department_id的职务平均薪金、以及该职务的总薪金，并为每个列给出合适的标题。

select job_id as "Job",
round(avg(case department_id when 20 then salary else null end), 2) as "Dept 20",
round(avg(case department_id when 50 then salary else null end), 2) as "Dept 50",
round(avg(case department_id when 80 then salary else null end), 2) as "Dept 80",
round(avg(case department_id when 90 then salary else null end), 2) as "Dept 90",
sum(salary) as "Total"
from hr.employees
group by job_id; ##### 文章目录 