Oracle中SQL语句技巧

一、SQL语句

1.1 子查询

---

首先子查询, 子查询可以作为查询条件,也可以作为要查询的表

1. 子查询需要添加括号
   ​​查询工资比SCOTT高的员工信息​​

SQL>select * from emp where sal > (select sal from emp where ename='SCOTT')

1. 合理的书写风格
2. 可以在where select having from后面 都可以使用子查询
3. 不可以在group by后面使用子查询
4. 强调from后面的子查询
5. 主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可
6. 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
7. 一般先执行子查询，再执行主查询；但相关子查询例外
8. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
9. 子查询中的null

特性	举例
子查询需要括号	查询工资比SCOTT高的员工信息: ​​SQL>select * from emp where sal > (select sal from emp where ename='SCOTT')​​
select后可以添加子查询	查询员工记录后，每一行后面显示总裁: ​​SQL> select empno,ename,sal,(select ename from emp where empno=7839) 总裁 from emp;​​
from后面的子查询	查询员工信息：员工号 姓名 月薪: ​​SQL> select * from (select empno,ename,sal from emp);​​ 查询员工信息：员工号 姓名 月薪 年薪: ​​SQL> select * from (select empno,ename,sal,sal*12 annsal from emp);​​ 上面的语句跟select empno,ename,sal,sal*12 annsal from emp性能一样的，oracle内部会重构
主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可	查询部门名称是SALES的员工，实现方法有两种: ​​SQL>select * from emp where deptno=(select deptno from dept where dname='SALES');​​ ​​SQL>select e.* from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';​​
in 在集合中	查询部门名称是SALES和ACCOUNTING的员工: ​​SQL> select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');​​
any: 和集合中的任意一个值比较	查询工资比30号部门到少一个员工高的员工信息【下面两行是等价】: ​​SQL> select * from emp where sal > any (select sal from emp where deptno = 30);​​​​SQL> select * from emp where sal > (select min(sal) from emp where deptno = 30);​​
all 和集合中的所有值比较	查询工资比30号部门所有员工高的员工信息【下面两行是等价】: ​​SQL> select * from emp where sal > all (select sal from emp where deptno = 30);​​​​SQL> select * from emp where sal > (select max(sal) from emp where deptno = 30);​​
not in(a,b,null) not in中不能有null in 中可以有null 多行子查询中的null	查询是老板（管理层）的员工: ​​SQL> select * from emp where empno in(select mgr from emp);​​ 查询不是老板(管理层)的员工: ​​SQL> select * from emp where empno not in(select mgr from emp);​​【not in中不能有null】 ​​SQL> select * from emp where empno not in(select mgr from emp where mgr is not null);​​【这个写法才正确】

1.1.1 from后的子查询

---

将子查询的内容作为一张表, 先进行子查询, 然后在进行select去查询子查询表中的数据

使用技巧:
​​​select * from (select name from emp where sex='男' order by age) where rownum=1;​​​ order by为最后执行, 如果这样写​​select name from emp where sex='男' and rownum=1 order by age;​​就不正确了, 目的是你想要排好序后的第一条数据, 如果不使用子查询, 就是没有排序, 取了第一条数据, 最后在排序就失去了意义, 这也算一个技巧吧.

1.2 查询时如何添加列来显示行号

---

​​select row_number() over(order by UserID) as rowNum, * from UserInfo​​

如果查询没有进行排序,可以直接使用row_number()函数来实现自动增加序号列

使用Oracle自带的row_number()函数能够实现自动增加序号列的要求，但是同时引发一个问题，如果我们查询出来的数据需要使用Order By排序的话，那么我们会发现新增加的序号列是乱序的，它会根据我们Order By后面的字段重新排序，那么怎么解决这一问题呢。

如果查询的数据进行排序,那么需要在row_number()函数后在加上order by排序就可以了

row_number() over()分组排序功能：

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

select row_number() over(ORDER BY t.taskcreatetime),
t.ACTIVITYNAME,
t.TASKTYPE 
from t_sys_flow_task t 
ORDER BY t.taskcreatetime;

原文内容:​​Oracle使用row_number()函数查询时增加序号列​​Oracle ROW_NUMBER() OVER()函数用法详解 （分组排序 例子多）

1.3 where条件中如何加判断

---

场景:
想要在oracle的sql语句where条件里添加判断:
如果t.riskcode为空,则不加判断条件;
如果t.riskcode不为空, 则加上t.riskcode=u.riskcode

解决方法:
1.​​​select distinct XX from emp t, emp2 u where t.riskcode is null or t.riskcode=u.riskcode​​​ 2.​​select distinct XX from emp t, emp2 u where u.riskcode=nvl(t.riskcode,u.riskcode)​​

1.4

下面的更新语句: 如果name的值不为空,则优先更新name不为空的数据, 否则更新name 为空的数据. 这里适用于where条件可以识别唯一一条数据,
​​​update emp set sex='boy' where age>10 and name='jack' or name is null and rownum=1;​​

1.5 查询语句和起别名如何加入特殊符号?

1. ​oracle别名中如何使用特殊字符（/，#，&）？​​别名两边加单引号试试​​

SQL> select rownum "S/N" from eric_tmp
  2  /

  S/N
---------
    1
    2
    3
    4
    5
    6
    7

7 rows selected.

当别名没有被双引号括起来时，其显示结果为大写。如果别名中包含了特殊字符，或想让别名原样显示，你就要使用双引号把别名括起来。

​​SELECT empno AS "Employee Number",ename name,(sal 500)*12 "Annual Salary" FROM emp;​​ 参考文章: SQL别名、Oracle字符串处理

2. 字符串拼接”||”
   我们把年薪后面加个$
   ​​​SELECT salary*12+COALESCE(bonus,0)||'$' AS annual_salary FROM TEST_LJB;​​​注意：字符串通过单引号包括。
   如果我们想要的结果是含有’（单引号）的呢？10000’$
   可以在增加一个’,加上需要显示的单引号，即两个单引号;
   或者使用 Oracle 特殊语法​​​SELECT q'/I'm Shangbo/' FROM DUAL;​​​​SELECT salary*12+COALESCE(bonus,0)||'''$' AS annual_salary FROM TEST_LJB;​​



3. 参考文章: Oracle学习2：别名&字符串拼接

1.7 ​​Oracle中*和其他字段组合查询的说明​​

在查询中，如果不仅要查某个表的所有字段，同时还要查询别的字段，此时是要为表加上别名才能使用*

例如：

select *,1 from student_drb------------------这样是不行的，会报错
为表加上别名即可

select a.*,1 from student_drb a

1.8 如何使用with…as…select 使用union all

通常with…as…select如下使用

with temp as 
  ( select id, name from student )
select name, subject from course,temp where course.cid=temp.id

使用兩個或多個with…as將結果union all

with temp1 as
  (select contno from lacommision),
  temp2 as
  (select contno from laboacommision)
select contno, wageno, agentcode from ladeduct a, temp1 where a.contno=temp1.contno
union all
select contno wageno, agentcode from ladeduct a, temp2 where a.contno=temp2.contno