--数据库版本
SEAN@sean> select * from v$version;BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production--建表
SEAN@sean> create table tasselect 'A' || mod(level, 3) grp, level val, sysdate + level / 24 crt_datefrom dualconnect by level <= 9;Table created.SEAN@sean> col grp for a10;
SEAN@sean> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';Session altered.SEAN@sean> select * from t order by grp,crt_date;GRP VAL CRT_DATE
---------- ---------- -------------------
A0 3 2017-07-25 11:13:41
A0 6 2017-07-25 14:13:41
A0 9 2017-07-25 17:13:41 --结果行1
A1 1 2017-07-25 09:13:41
A1 4 2017-07-25 12:13:41
A1 7 2017-07-25 15:13:41 --结果行2
A2 2 2017-07-25 10:13:41
A2 5 2017-07-25 13:13:41
A2 8 2017-07-25 16:13:41 --结果行39 rows selected.SEAN@sean> exec dbms_stats.gather_table_stats(user,'T');PL/SQL procedure successfully completed.--需求
求每组时间(crt_date)最大的那个值(val),结果字段命令为 result_val 和 result_crt_date--如果 order by t.crt_date 不能唯一确定排序,那么结果可能不正确。这时可以加入 rowid 辅助排序 order by t.crt_date,rowid,本例假设 order by t.crt_date 能唯一确定排序--方法1 row_number()
SEAN@sean> select grp, val result_val, crt_date result_crt_datefrom (select t.grp,t.val,t.crt_date,row_number() over(partition by t.grp order by crt_date desc) rnfrom t)where rn = 1;GRP RESULT_VAL RESULT_CRT_DATE
---------- ---------- -------------------
A0 9 2017-07-25 17:13:41
A1 7 2017-07-25 15:13:41
A2 8 2017-07-25 16:13:41--方法2 (last + order by asc)
SEAN@sean> select t.grp,max(t.val) keep(dense_rank last order by t.crt_date asc) result_val,max(t.crt_date) keep(dense_rank last order by t.crt_date asc) result_crt_datefrom tgroup by t.grp;GRP RESULT_VAL RESULT_CRT_DATE
---------- ---------- -------------------
A0 9 2017-07-25 17:13:41
A1 7 2017-07-25 15:13:41
A2 8 2017-07-25 16:13:41--方法3 (first + order by desc)
SEAN@sean> select t.grp,max(t.val) keep(dense_rank first order by t.crt_date desc) result_val,max(t.crt_date) keep(dense_rank first order by t.crt_date desc) result_crt_datefrom tgroup by t.grp;GRP RESULT_VAL RESULT_CRT_DATE
---------- ---------- -------------------
A0 9 2017-07-25 17:13:41
A1 7 2017-07-25 15:13:41
A2 8 2017-07-25 16:13:41--方法3.1 (first + order by desc) 的分析函数写法,每组返回多行数据
SEAN@sean> select t.grp,t.val,t.crt_date,max(t.val) keep(dense_rank first order by t.crt_date desc) over(partition by t.grp) result_val,max(t.crt_date) keep(dense_rank first order by t.crt_date desc) over(partition by t.grp) result_crt_datefrom t;GRP VAL CRT_DATE RESULT_VAL RESULT_CRT_DATE
---------- ---------- ------------------- ---------- -------------------
A0 3 2017-07-25 11:13:41 9 2017-07-25 17:13:41
A0 9 2017-07-25 17:13:41 9 2017-07-25 17:13:41
A0 6 2017-07-25 14:13:41 9 2017-07-25 17:13:41
A1 1 2017-07-25 09:13:41 7 2017-07-25 15:13:41
A1 7 2017-07-25 15:13:41 7 2017-07-25 15:13:41
A1 4 2017-07-25 12:13:41 7 2017-07-25 15:13:41
A2 8 2017-07-25 16:13:41 8 2017-07-25 16:13:41
A2 2 2017-07-25 10:13:41 8 2017-07-25 16:13:41
A2 5 2017-07-25 13:13:41 8 2017-07-25 16:13:419 rows selected.--If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
--对于分析函数,省略窗口子句,默认值是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,也就是第一行到当前行--方法4 (last_value)
select t.grp,t.val,t.crt_date,last_value(t.val) over(partition by t.grp order by t.crt_date asc range between unbounded preceding and unbounded following) result_val,last_value(t.crt_date) over(partition by t.grp order by t.crt_date asc range between unbounded preceding and unbounded following) result_crt_datefrom t;--方法5 (first_value)
select t.grp,t.val,t.crt_date,first_value(t.val) ignore nulls over(partition by t.grp order by t.crt_date desc range between unbounded preceding and unbounded following) result_val,first_value(t.crt_date) ignore nulls over(partition by t.grp order by t.crt_date desc range between unbounded preceding and unbounded following) result_crt_datefrom t;--方法6 (nth_value)
select t.grp,t.val,t.crt_date, nth_value(t.val, 1)from last ignore nulls over(partition by t.grp order by t.crt_date asc rows between unbounded preceding and unbounded following) result_val,nth_value(t.crt_date, 1)from last ignore nulls over(partition by t.grp order by t.crt_date asc rows between unbounded preceding and unbounded following) result_crt_datefrom t;总结:使用内置函数避免一些中间视图的构造和表的自连接
详细解决方案
Oracle SQL 分析函数 first、last、first_value、last_value、nth_value
热度:8 发布时间:2024-01-16 02:21:34.0
相关解决方案
- oracle 分页排序,ssi,该怎么处理
- oracle 最近的时间(而不是前一天的时间) 跪求sql语句 。解决方法
- oracle,该怎么解决
- oracle 安装时出现 java tm 异常
- android访问其他数据库(如:oracle、MySql等),希望大家给点建议!解决方案
- oracle 安插 LONG VARCHAR 类型数据
- oracle loadjava如何用
- oracle,该如何处理
- C# + SQL server +oracle QQ交流群142703980解决方法
- VS10连接Oracle的有关问题:"Oracle.DataAccess.Client.OracleConnection"的类型初始值设定项引发错误
- Oracle-参数查询有关问题
- oracle,insert 语句插入一条记录后要求,返回主键id値。解决思路
- Oracle 10g下的SQL语句,有点难度.请~[同时向朋友们道歉~]
- 月统计(oracle)解决办法
- 在线紧急等一sql语句(oracle),该怎么处理
- oracle 连接数据库该如何写语句的
- oracle 客户端链接服务端的方式?该如何解决
- Oracle select后表的最左首的数字
- Oracle 服务器字符集和pl/sql字符集有关问题,
- Oracle DBA解决方案
- oracle 中表名的连接解决方法
- oracle 老鸟进,新人求指点解决方法
- oracle 恢复删除的表,该怎么解决
- oracle 数据自动增长,该怎么解决
- 求推荐数据库脚本编纂的书籍(oracle)
- oracle 数据库,该如何解决
- oracle 用室外部验证
- 高分寻 oracle 锁机制 并行安插表数据数量的控制方法
- oracle 中表名的联接
- oracle 11G 企业版跟标准版有什么区别