有用的SQL语句 oracle 递归、分页、分组

1.在数据库设计中，经常要设计出树形结构的表，比如一些产品代码表等

如下图所示：

 

表test

id fid name

1 -1 交通运输设备

2 1 小轿车

3 1 越野汽车

4 2 普通轿车

5 2 高级轿车

6 -1 专用设备

 

select id,p_id,name from test start with id='1' connect by PRIOR id=fid order by id

这样的SQL语句就能够查询出id为1节点及其下面所有节点的信息！该语法在Oracle数据库中可以，但在MySQL数据库中会提示语法错误！

 

2.如果大家要为公司搭建基础平台的话，往往会写一个分页的组件，在这个组件中要对sql进行分页查询，这里提供2个分页查询的sql

一、

select *

from (select id, name, rownum r from t where rownum <= 20)

where r > =11

这是一种查询效率最高的sql分页查询的写法了，语句中有2个select，嵌套了一次，它的缺点是查询的sql与分页功能的sql耦合在一起，不利于程序的使用，其中查询的sql只是select id, name from t

二、

SELECT *

FROM (SELECT a.*, ROWNUM AS ROWNO

FROM (select id, name from t ) a where rownum<=20 ) b

where b.rowno >=11

这个sql语句做了两次嵌套，效率没有上面的写法高，但是把查询的sql语句分离了出来，便于在程序中使用，推荐在程序中使用这种分页sql。

注意：在第二层select中要限定rownum的上限，是为了提高最后一层查询的效率，最后一层查询只是在20条记录中做筛选了，缩小了范围，提高了效率。

 

3、分组查询，对group by的使用

有这样的一个需求，要查询出每个客户最新的序列号的信息， 数据如下：

test表

khid（客户id） yxq_q（有效期起） yxq_z（有效期止） xlh（序列号）

1 2006-1-1 2007-1-1 111

1 2007-1-1 2008-1-1 222

2 2007-5-4 2008-5-4 333

2 2008-5-4 2009-5-4 444

3 2009-1-1 2010-1-1 555

想要查询出的结果为：

khid yxq_q yxq_z xlh

1 2007-1-1 2008-1-1 222

2 2008-5-4 2009-5-4 444

3 2009-1-1 2010-1-1 555

 

sql：select t.*
from test t,
(select t.khid tkhid, max(yxq_z) tyxqz from test t group by t.khid) tmp
where t.khid = tmp.tkhid
and t.yxq_z = tmp.tyxqz

sql编写的思路正确！ 经过验证，查询结果正确！

 

group by的理解，他能够按照某个字段进行分组，比如上面的例子就是按照khid进行了分组，这里有个粒度的概念，就是按照某些字段进行分组后的组数较少，就说这个粒度较粗，反之，如果按照某个字段进行分组后的组数较多，就说这个粒度较细。我们也能看到些复杂的分组，是以两个字段来进行分组，group by字段1（粗粒度），字段2（细粒度），并不是以字段2（细粒度）分组的结果为最后的分组结果，而是在按照一个字段进行分组后，在按照另一个字段进行分组。是2次分组的结果叠加！