oracle 中的ROW_NUMBER() OVER() 的用法以及如何把空值转化指定值——记一次查询
来源:互联网 发布:ubuntu 装win10 编辑:程序博客网 时间:2024/06/16 08:17
- 前提条件与需求
- oracle 中的 top 怎么实现
- ROW_NUMBER OVER 分析函数的用法
- 如何把控制转化成指定值
- 最后的终结版sql
前提条件与需求
主表: ag_ba_old_base
从表: AG_BA_OLD_MEDICAL
字典表: jz_dictionary
在从表中有ag_ba_old_base_id 这个字段是和主表关联的,但是由于各种各样的原因导致从表中有很多重复数据,我们需要从中筛选出同样ag_ba_old_base_id 中 最新的更新日期 ,如果没有更新日期那么用id来比较,id比较大的优先留着。
如下图
select t.ag_ba_old_base_id ,count(id) from AG_BA_OLD_MEDICAL t where t.create_date is not null group by t.ag_ba_old_base_id having count(id) >3
实际上重复两次和三次的也很多 这里只是为了演示一下重复数据
我们找一个重复次数比较多的数据来看
select t.id,t.ag_ba_old_base_id,t.create_datefrom AG_BA_OLD_MEDICAL t where ag_ba_old_base_id = 81904287order by t.ag_ba_old_base_id,t.create_date desc,t.id desc
这里我们可以看到重复数据之间的区别是 create_date 和此表类似于主键的id(之所以这么说是因为此表非常不规范 不能按照主键处理),而且接到任务时被告知这表中可能存在id小但是create 但是create_date 更新的情况。所以在筛选时不能单单利用max()函数来直接分组查询,这给这次查询增加了一点难度。但是经过仔细思考发现用order by 完全可以解决
order by t.ag_ba_old_base_id,t.create_date desc,t.id desc
经过查询发现oracle 和mysql很不一样,其中有一点就是mysql中的top 在oracle中是需要借助子查询才能实现,具体情况如下
oracle 中的 top 怎么实现
参考http://www.cnblogs.com/MyFavorite/archive/2012/10/31/2748936.html
在SQL Server里面有top关键字可以很方便的取出前N条记录,但是Oracle里面却没有top的使用,类似实现取出前N条记录的简单方法如下:
方法1:利用ROW_NUMBER函数
SELECT NO FROM ( SELECT ROW_NUMBER() OVER (ORDER BY NO) RNO, NO FROM ROWNUM_TEST)WHERE RNO <= 5 ORDER BY NO ;
方法2:利用子查询
取出前5条记录:
SELECT NO FROM ( SELECT NO FROM ROWNUM_TEST ORDER BY NO)WHERE ROWNUM <= 5 ORDER BY NO ;
第二种情况经过测试发现并没有第一种好用,因为rownum是数据在原始表中的顺序,并不是此次select的数据的顺序。
那么第一种我们来看看它是怎么用的
ROW_NUMBER() OVER() 分析函数的用法
参考http://blog.csdn.net/iw1210/article/details/11937085
ROW_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内是连续且唯一的)。
举例:
SQL> DESC T1;
Name Null? Type
ID NUMBER
NAME VARCHAR2(10)
DATE1 DATE
SQL> SELECT * FROM T1;
ID NAME DATE1 101 aaa 09-SEP-13 101 bbb 10-SEP-13 101 ccc 11-SEP-13 102 ddd 08-SEP-13 102 eee 11-SEP-13
SQL> SELECT ID,NAME,DATE1,ROW_NUMBER() OVER(partition by ID order by DATE1 desc) as RN FROM T1;
ID NAME DATE1 RN 101 ccc 11-SEP-13 1 101 bbb 10-SEP-13 2 101 aaa 09-SEP-13 3 102 eee 11-SEP-13 1 102 ddd 08-SEP-13 2
把上面语句作为一个子表语句,嵌入到另一条语句中:
SQL> SELECT ID,NAME,DATE1 FROM (SELECT ID,NAME,DATE1,ROW_NUMBER() OVER(partition by ID order by DATE1 desc) as RN FROM T1)T WHERE T.RN=1;
ID NAME DATE1 101 ccc 11-SEP-13 102 eee 11-SEP-13
那么最终经过思考写出的sql语句如下
select base.id,base.name,c.id,c.ag_ba_old_base_id,jz.name from ag_ba_old_base baseleft join ( select id,ag_ba_old_base_id,create_date,medical_ensure_type from ( select t.id,t.ag_ba_old_base_id,t.medical_ensure_type,t.create_date,ROW_NUMBER() OVER(partition by t.ag_ba_old_base_id order by t.ag_ba_old_base_id,t.create_date desc,t.id desc) as asd from AG_BA_OLD_MEDICAL t where ag_ba_old_base_id = t.ag_ba_old_base_id )b where asd =1 )con c.ag_ba_old_base_id = base.idleft join jz_dictionary jz on jz.id = c.medical_ensure_type
结果如下图
发现最终的结果中有空的情况存在,也就是null ,所以要借助下面这个函数
如何把控制转化成指定值
NVL(Expr1,Expr2)如果Expr1为NULL,返回Expr2的值,否则返回Expr1的值
例如:select NVL(SUM(MONEY) ,0) from tb全都在NVL这儿起作用
其它:
NVL2(Expr1,Expr2,Expr3)如果Expr1为NULL,返回Expr2的值,否则返回Expr3的值
NULLIF(Expr1,Expr2)如果Expr1和Expr2的值相等,返回NULL,否则返回Expr1的值
最后的终结版sql
所以最终版的sql就如下:
select base.id,base.name,c.id,c.ag_ba_old_base_id,NVL(jz.name,'未知')as name from ag_ba_old_base baseleft join ( select id,ag_ba_old_base_id,create_date,medical_ensure_type from ( select t.id,t.ag_ba_old_base_id,t.medical_ensure_type,t.create_date,ROW_NUMBER() OVER(partition by t.ag_ba_old_base_id order by t.ag_ba_old_base_id,t.create_date desc,t.id desc) as asd from AG_BA_OLD_MEDICAL t where ag_ba_old_base_id = t.ag_ba_old_base_id )b where asd =1 )con c.ag_ba_old_base_id = base.idleft join jz_dictionary jz on jz.id = c.medical_ensure_type
- oracle 中的ROW_NUMBER() OVER() 的用法以及如何把空值转化指定值——记一次查询
- ORACLE 中的 ROW_NUMBER() OVER() 分析函数的用法
- ORACLE 中的 ROW_NUMBER() OVER() 分析函数的用法
- 记录---ORACLE 中的 ROW_NUMBER() OVER() 分析函数的用法
- ORACLE 中的 ROW_NUMBER() OVER() 分析函数的用法
- Oracle 中的 ROW_NUMBER() OVER() 分析函数的用法
- ORACLE 中的 ROW_NUMBER() OVER() 分析函数的用法
- oracle row_number over()函数用法
- oracle 中的 ROW_NUMBER() OVER() 函数
- Oracle ROW_NUMBER() OVER函数的基本用法用法
- decode和instr的用法 以及 row_number() over语法
- ROW_NUMBER() OVER函数的基本用法(oracle)
- row_number() over()的一个用法
- ROW_NUMBER() OVER 的基本用法
- oracle的 row_number() over()应用
- oracle中的rank() over,dense_rank(),row_number()的区别
- oracle row_number() over()分析函数用法
- oracle中row_number() over()分析函数用法
- UrlConnection使用
- DHCP option 60 和 option 61
- eclipse创建maven项目报错
- ubuntu16中遇到libgstreamer-0.10.so.0缺失解决方案 qt引入webkitwidgets 模块后提示缺少libgstreamer
- python函数使用
- oracle 中的ROW_NUMBER() OVER() 的用法以及如何把空值转化指定值——记一次查询
- iOS多线程pthread
- Windows 下的删除含有众多文件夹的代码目录操作
- $(window).width()与window.innerWidth的区别
- Centos里的tomcat开启https接口
- asp.net mvc下使用FluentScheduler 后台任务定时器
- 搭建自己的ES6转ES5环境
- OkHttpGET+Post+异步GET+异步POST请求数据
- DeepLearning tutorial 易用的深度学习框架Keras简介