常用的oracle查询语句

将数据根据月份分组查询且从小到大排序

这个适用于echarts的数据需要

1
2
3
4
5
select to_char(t.create_time, 'yyyy-mm') 月份,count(*) 数量 
from T_SHARE_CERT_ANALYZE t
where t.create_time between to_date('2020-01', 'yyyy-mm') and to_date('2020-12','yyyy-mm')
group by to_char(t.create_time, 'yyyy-mm')
order by to_char(t.create_time, 'yyyy-mm');

相关子查询

把 select deptId,name from t_sys_dept where STATUS = ‘1’查询出的数据做为 bs 表,再找满足两表要求的数据 select count(1)from BUSINESSINFO t where t.deptId = bs.deptId and t.type = ‘5’

1
2
3
4
5
6
7
8
9
10
select bs.name, (select count(1)from BUSINESSINFO t where t.deptId = bs.deptId and t.type in ('1','2')) AS pc,
(select count(1)from BUSINESSINFO t where t.deptId = bs.deptId and t.type = '5') AS phone,
(select count(1)from BUSINESSINFO t where t.deptId = bs.deptId and t.status = '0') AS zb,
(select count(1)from BUSINESSINFO t where t.deptId = bs.deptId and t.status = '1') AS bj,
(select count(1)from BUSINESSINFO t where t.deptId = bs.deptId and t.status = '0') AS zb,
(select count(1)from BUSINESSINFO t where t.deptId = bs.deptId and t.type in ('1','2') and t.createtime = sysdate) AS pcd,
(select count(1)from BUSINESSINFO t where t.deptId = bs.deptId and t.type = '5' and t.createtime = sysdate) AS phoned,
(select count(1)from BUSINESSINFO t where t.deptId = bs.deptId and t.status = '0' and t.createtime = sysdate) AS zbd
From
(select deptId,name from t_sys_dept where STATUS = '1') bs

嵌套子查询

  1. 查询刚好通过 60 分的 所有学生
1
2
3
4
select * from student where stuNo in
(
select stuNo from stuMarks where score = 60
);
  1. 在商业出版社价格范围内
1
2
3
4
5
-- distinct 去重
select * from publishers where pub_id in
(
select distinct pub_id from titles where lower(type) = 'business'
)
  1. 查询薪水小于工作岗位 CLERK 的任何一个薪资的员工信息并且不包含工作岗位为 CLERK 的员工信息
1
2
select * from emp
where sal < any (select sal from emp where job='CLERK') and job<>'CLERK';

case 语句

增加一个字段, 且以美式打分法打分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- case结构: 类似于switch语句
select stumarks.*,
case
when writternexam >= 90 then
'A'
when writternexam >= 80 then
'B'
when writternexam >= 70 then
'C'
when writternexam >= 60 then
'D'
else
'E'
end as 等级
from stumarks;

exists 存在性验证

exists 表示()内子查询语句返回结果不为空, 返回 true, 就会执行主 sql 语句,
如果为空, 返回 false ,sql 语句就不会执行。

not exists 和 exists 相反,子查询语句结果为空,则表示 where 条件成立,执行 sql 语句。负责不执行。

1
2
3
4
select * from student where exists
(
select * from stumarks where writernExam < 60 or labexam < 60
);

exist 与 in

  1. 查询存在员工的部门信息
1
2
3
select dep.depid, dep.depname
from department dep
where exists (select * from employee emp where emp.depid = dep.depid)


1
2
3
select d.depid, d.depname
from department d
where d.depid in (select e.depid from employee e)

  1. 查询部门平均工资不小于 3000 元的部门信息(未过滤没有员工的部门信息)
1
2
3
4
5
select avg(empsalary) avgsal, e.depid, d.depname
from employee e
left join department d
on d.depid = e.depid
group by e.depid, d.depname;

可以使用 not exists 来实现:

1
2
3
4
5
6
7
select *
from department de
where not exists (select em.depid
from employee em
where em.depid = de.depid
group by em.depid
having(avg(em.empsalary) < 3000))

同 not in

1
2
3
4
5
6
7
select depa.depid, depa.depname
from department depa
where depa.depid not in
(select empl.depid
from employee empl
group by empl.depid
having(avg(empl.empsalary) < 3000))

如果需要过滤掉没有员工的部门,可以进行一下改造。

  1. 查询部门平均工资不小于 3000 元的部门信息(过滤掉没有员工的部门信息)
1
2
3
4
5
6
7
8
9
select *
from department de
where not exists (select em.depid
from employee em
where em.depid = de.depid
group by em.depid
having(avg(em.empsalary) < 3000))
and exists
(select * from employee empl where empl.depid = de.depid)

同样我们也可以用 not in 来实现:

1
2
3
4
5
6
7
8
select depa.depid, depa.depname
from department depa
where depa.depid not in
(select empl.depid
from employee empl
group by empl.depid
having(avg(empl.empsalary) < 3000))
and depa.depid in (select emplo.depid from employee emplo)

分页使用(查询某个范围的数据)

  1. 使用rownum 虚列

按姓名排序取出前十条数据

1
select id,name from (select id,name from student order by name) where rownum<=10 order by rownum asc
  1. 最佳选择:利用分析函数

取出100-150条记录,按照tname排序

1
2
3
 select tname,tabtype from (
select tname,tabtype,row_number() over ( order by tname ) rn from tab
) where rn between 100 and 150;
打赏
  • 版权声明: 本博客所有文章除特别声明外,均采用 Apache License 2.0 许可协议。转载请注明出处!
  1. © 2020 Liu Yang    湘ICP备20003709号

请我喝杯咖啡吧~

支付宝
微信