技术心得
一、
SQL
查询:
1
、
”
列出同部门中工资高于
1000
的员工数量超过
2
人的部门,显示部门名字、地区名称
”
.
查询语句如下:
select
distinct
dept.department_name,loc.city
from
employees emp,departments dept,locations loc
where
emp.department_id=dept.department_id
and
dept.location_id=loc.location_id
and
EXISTS
(
select
1
from
employees emp2
where
emp2.department_id=emp.department_id
and
emp2.salary>
1000
group
by
emp2.department_id
having
count
(emp2.department_id)>
2
)
通常我们只需要对记录逐行的筛选,对于分组数据我们只能使用分组语句
avg
、
max
等
,
也就是说如果你想在
select
中得到这个属性那么“它们“也必须出现在
group
by
中或者你只想得到一个统计数据
.
回顾上面的例子
,
我们也可以用以下语句完成
:
select
dept.department_name,loc.city,
count
(*)
from
employees emp,departments dept,locations loc
where
emp.department_id=dept.department_id
and
dept.location_id=loc.location_id
and
emp.salary>
1000
group
by
dept.department_name,loc.city
having
count
(*) >
2
对于
group by
来说每一条
emp.department_id
必对应唯一
dept.department_id
、
dept.department_name
因此不论
group by department_id
还是
group by department_name,loc.city
达到的效果是一样的
2
、用一条语句查询出
scott.emp
表中每个部门工资前三位的数据
:
select department_id,max(salary) max_salary,max(decode (rank,2,salary,salary))mid_salary,min(salary) min_salary
from
(
select department_id,salary,rank from
(select emp.department_id,emp.employee_id,emp.salary,row_number()over(partition by emp.department_id order by emp.salary)
as rank
from employees emp) E
where E.rank<=3
)
group by department_id