初学SQL*PLUS,写的这个表查询运行很慢,结果一定是错的,为什麽 ?!
问题描述:
创建一个查询显示雇员总数,和在 1995、1996、1997 和1998受雇的雇员人数。创建适当的列标题。 SELECT COUNT(a.employee_id) AS"TATAL",
COUNT(b.employee_id) AS"1995",
COUNT(c.employee_id) AS"1996",
COUNT(d.employee_id) AS"1997",
COUNT(e.employee_id) AS"1998"
FROM employees a,
(SELECT employee_id FROM employees WHERE hire_date > '31-12月-95') b,
(SELECT employee_id FROM employees WHERE hire_date > '31-12月-96') c,
(SELECT employee_id FROM employees WHERE hire_date > '31-12月-97') d,
(SELECT employee_id FROM employees WHERE hire_date > '31-12月-98') e
问题描述:
创建一个查询显示雇员总数,和在 1995、1996、1997 和1998受雇的雇员人数。创建适当的列标题。 SELECT COUNT(a.employee_id) AS"TATAL",
COUNT(b.employee_id) AS"1995",
COUNT(c.employee_id) AS"1996",
COUNT(d.employee_id) AS"1997",
COUNT(e.employee_id) AS"1998"
FROM employees a,
(SELECT employee_id FROM employees WHERE hire_date > '31-12月-95') b,
(SELECT employee_id FROM employees WHERE hire_date > '31-12月-96') c,
(SELECT employee_id FROM employees WHERE hire_date > '31-12月-97') d,
(SELECT employee_id FROM employees WHERE hire_date > '31-12月-98') e
TOTAL | 1995| 1996| 1997| 1998
100 20 20 30 30
from employee
where hire_date between to_date('1995-01-01', 'yyyy-mm-dd') and
to_date('1998-12-31', 'yyyy-mm-dd')
group by to_char(hiredate, 'yyyy-mm-dd')
sum(case when a.hire_date > date '1995-01-01' then 1 else 0 end) 1995,
sum(case when a.hire_date > date '1996-01-01' then 1 else 0 end) 1996,
sum(case when a.hire_date > date '1997-01-01' then 1 else 0 end) 1997,
sum(case when a.hire_date > date '1998-01-01' then 1 else 0 end) 1998,
FROM employees a
sum(case when a.hire_date > date '1995-01-01' then 1 else 0 end) 1995,
sum(case when a.hire_date > date '1996-01-01' then 1 else 0 end) 1996,
sum(case when a.hire_date > date '1997-01-01' then 1 else 0 end) 1997,
sum(case when a.hire_date > date '1998-01-01' then 1 else 0 end) 1998,
FROM employees a
---------- ----------
1995-05-01 1
1995-05-18 1
1995-07-14 1
1995-10-17 1
1996-01-27 1
1996-01-30 1
1996-02-04 1
1996-02-17 1
1996-03-04 1
1996-05-11 1
1996-06-14 1
格式不是要这样子的。
ORA-00923: 未找到预期 FROM 关键字
sum(case when a.hire_date > date '1995-01-01' then 1 else 0 end) 1995,
sum(case when a.hire_date > date '1996-01-01' then 1 else 0 end) 1996,
sum(case when a.hire_date > date '1997-01-01' then 1 else 0 end) 1997,
sum(case when a.hire_date > date '1998-01-01' then 1 else 0 end) 1998
FROM employees a
TATAL 1995 1996 1997 1998
---------- ---------- ---------- ---------- ----------
1161763200 1161763200 1161763200 1161763200 1161763200
sum(case when a.hire_date>date'1995-01-01 then 1 else 0 end')1995,
.
.
.
from employees a;