【Oracle】sql的使用总结

1，分组排序取每组的第一个
示例：

select * from (select row_number() over(partition by ROADID order by YDSJ desc) LEV,YDLD.* from YDLD where YDSJ between to_date('2017/07/01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2017/07/02 00:00:00','yyyy-mm-dd hh24:mi:ss')) where LEV=1

该sql的意思是，按照roadid分组，同时根据里面的ydsj（这个是时间类型）倒序，这里的lev就是指的就是组内的第几个。

2，按照小时数分组
示例

select substr(to_char(jtjq.jjsj,'yyyymmddhh24'),9)+1 num,count(*) count from jtjq where jjsj>(select sysdate-1 from dual) group by to_char(jtjq.jjsj,'yyyymmddhh24') order by num

上面的jtjq是表名，jjsj是里面的一个字段，这个是按照24小时制进行分组的，从1开始计数，直到24。

3，三表连接（在两表连接外再包一层）
示例

select t3.*,t4.mjxm,t4.sfzh,t4.sjhm,t4.mjlb,t4.bmlx from ( select t1.*,t2.jgmc from (select * from ( select row_number() over (partition by MJJH order by uploaddate desc) lev,t.* from TB_GPS t where uploaddate between to_date('2016/07/22 00:00:00' ,'yyyy/mm/dd hh24:mi:ss') and to_date('2016/07/22 10:48:13' ,'yyyy/mm/dd hh24:mi:ss')) where lev = 1 ) t1 inner join VW_CT_WF_SYBB_ZD t2 on t1.JGBM = t2.JGBM) t3 inner join psms.TP_USERS t4 on t3.MJJH=t4.MJJH

这里的join是inner join，但是实际上有几种jion，分别是left join，right join，inner join，full join。

a left join b，就是以a为基准，a表查询出来的字段都有，a没有的不显示。
a right join b，就是以b为基准，b表查询出来的字段都有，b没有的不显示。
a inner join b，就是以两者共有的部分为基准，除了共有之外的部分不显示。
a full join b，就是以两者全部的集合为基准，显示所有部分。

具体语法和使用参考：

SQL的JOIN语法解析(inner join, left join, right join, full outer join的区别)

4，条件取值（NVL函数）
示例：nvl(a,b)
该函数表示，如果a为null，就取值为b。这个函数可以用来对于查询结果进行过滤，转换成自己想要的形式。

5，虚表dual的使用
这个可以用来生成一个虚表，使用方式：
select xxxx from dual
这里的dual可以任意指定一个确定的数字或者字符串等等。
例如：
SQL> select 1 from dual;

1
—-
1

该写法可以结合别的操作符（比如：merge into）使用

merge into c##zhjginfo.ZDCL_YCDZ_WFSTA t1 using (select ? hp,? hpzl from dual) t2 on (t1.hp=t2.hp and t1.hpzl=t2.hpzl) when matched then update set t1.syr=?,t1.cllx=?,t1.wf_num=?,t1.dri_num=?,t1.xzqh=? when not matched then insert values(?,?,?,?,?,?,?)

上面的？是在程序中绑定变量使用的。这个sql就是利用了dual虚表，进行两表之间的匹配，如果不用虚表，merge into 没有相应的表进行匹配。该sql适合在程序的循环中，获取到hp,hpzl这两个变量值之后，进行动态绑定，然后批量提交。

6，下划线问题
因为在sql中使用like进行模糊匹配的时候，可能需要判断列值中是否有下划线，但是因为下划线在sql的模糊匹配中又代表匹配单个任意字符。因此要匹配某个字段是否包含下划线的时候，需要进行转义。
如下：

7，生成uuid
示例：

update test set myuuid=sys_guid()

8，多表关联更新
示例：

update customers a -- 使用别名set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id) where exists (select 1 from tmp_cust_city b where b.customer_id=a.customer_id) -- update 超过2个值update customers a -- 使用别名set (city_name,customer_type)=(select b.city_name,b.customer_type from tmp_cust_city b where b.customer_id=a.customer_id) where exists (select 1 from tmp_cust_city b where b.customer_id=a.customer_id)

9，with as来创建临时表
适用场景：
将几个查询结果作为最终查询结果的”中间件“，例如下表中的t1，t2作为最终结果的数据源。
示例：

with  t1 as (select to_char(to_date('2016/8/1','yyyy/mm/dd')+level-1,'yyyy/mm/dd') d1  from dual connect by level<=(to_date('2016/8/31','yyyy/mm/dd')-to_date('2016/8/1','yyyy/mm/dd')+1) order by d1),  t2 as (select count(*) totalnum,to_char(sgfssj,'yyyy/mm/dd') d2 from sde.acd_dutysimple where (sgfssj between to_date('2016/8/1 00:00:00','yyyy/mm/dd hh24:mi:ss') and to_date('2016/8/31 23:59:59','yyyy/mm/dd hh24:mi:ss')) and (xzqh='420101' or xzqh='420106') group by to_char(sgfssj,'yyyy/mm/dd') order by to_char(sgfssj,'yyyy/mm/dd'))select t1.d1,sum(t2.totalnum) numall from t1 left join t2 on t1.d1=t2.d2 group by t1.d1 order by t1.d1

10，select into和insert into
前者可以用来直接生成一个备份表，后者用来插入数据，但是后者也可以用来生成一个备份表（insert into t1 select * from t2）
示例：
不同在于，前者不要求要插入的那张备份表存在，而后者要先创建一个备份表，然后才能插入。

SELECT vale1, value2 into Table2 from Table1

Insert into Table2(a, c, d) select a,c,5 from Table1

11，使用hint进行强制索引
我们知道oracle有两种优化器：
RBO（Rule-Based Optimization）基于规则的优化和CBO（Cost-Based Optimization）基于代价的优化。
而现在的oracle默认使用的是CBO，也就是数据库自己会判断执行哪种执行策略达到更好的效率，但由于某些原因，可能数据库自己的执行策略并不一定最优，因此，我们有时候需要自己定义规则，让数据库根据我们的规则来进行查询。比如利用我们定义好的索引进行强制索引（也就是告诉数据库要按照我指定的索引来进行索引）。下面是利用hint进行强制索引的示例：

select /*+index(index_name)*/ jszh,hphm,hpzl from table