oracle number最大值_SQL分组求最大值

1、表test数据如下图所示

2、求pname列相同的同时，vno值最大的行(可能是多行)

SELECT
    *
FROM
    (
        SELECT
            pname,
            vno,
            nname,
            tname,
            MAX(vno) OVER(
                PARTITION BY pname
                ORDER BY
                    pname
            ) num
        FROM
            test
    )
WHERE
    vno = num;

3、分析函数介绍

分析函数，也叫开窗函数，可以达到数据偏移，数据累计，同环比等等功能，在平时的开发中，特别是在报表设计开发中非常有用，简化了代码量，并且这些函数是oracle的内置函数，效率会更高。
分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows)
他们的使用形式如下：分析函数名(参数) over (partition by 子句 order by 子句 rows/range.. 子句) (注：若窗口函数内和sql语句末尾共存在两个order by
a) order by 字段两者一致：即sql语句中的order by子句里的内容和开窗函数over()中的order by子句里的内容一样， 那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；
b) order by 字段两者不一致：即sql语句中的order by子句里的内容和开窗函数over()中的order by子句里的内容不一样， 那么sql语句中的排序将最后在分析函数分析结束后执行排序。)

注意Partition by可以有多个字段。

4、常用的分析函数

1. count() over()  ：统计分区中各组的行数，partition by 可选，order by 可选

select ename,esex,eage,count(*) over() from emp; --总计数
select ename,esex,eage,count(*) over(order by eage) from emp; --递加计数
select ename,esex,eage,count(*) over(partition by esex) from emp; --分组计数
select ename,esex,eage,count(*) over(partition by esex order by eage) from emp;--分组递加计数

2. sum() over()  ：统计分区中记录的总和，partition by 可选，order by 可选

select ename,esex,eage,sum(salary) over() from emp; --总累计求和
select ename,esex,eage,sum(salary) over(order by eage) from emp; --递加累计求和
select ename,esex,eage,sum(salary) over(partition by esex) from emp; --分组累计求和
select ename,esex,eage,sum(salary) over(partition by esex order by eage) from emp; --分组递加累计求和

3. avg() over()  ：统计分区中记录的平均值，partition by 可选，order by 可选

select ename,esex,eage,avg(salary) over() from emp; --总平均值
select ename,esex,eage,avg(salary) over(order by eage) from emp; --递加求平均值
select ename,esex,eage,avg(salary) over(partition by esex) from emp; --分组求平均值
select ename,esex,eage,avg(salary) over(partition by esex order by eage) from emp; --分组递加求平均值

4、min() over() ：统计分区中记录的最小值，partition by 可选，order by 可选
max() over() ：统计分区中记录的最大值，partition by 可选，order by 可选

select ename,esex,eage,salary,min(salary) over() from emp; --求总最小值
select ename,esex,eage,salary,min(salary) over(order by eage) from emp; --递加求最小值
select ename,esex,eage,salary,min(salary) over(partition by esex) from emp; --分组求最小值
select ename,esex,eage,salary,min(salary) over(partition by esex order by eage) from emp; --分组递加求最小值


select ename,esex,eage,salary,max(salary) over() from emp; --求总最大值
select ename,esex,eage,salary,max(salary) over(order by eage) from emp; --递加求最大值
select ename,esex,eage,salary,max(salary) over(partition by esex) from emp; --分组求最大值
select ename,esex,eage,salary,max(salary) over(partition by esex order by eage) from emp; --分组递加求最大值

5. rank() over()  ：跳跃排序，partition by 可选，order by 必选

select ename,eage,rank() over(partition by job order by eage) from emp;
select ename,eage,rank() over(order by eage) from emp;

6. dense_rank() ：连续排序，partition by 可选，order by 必选

select ename,eage,dense_rank() over(partition by job order by eage) from emp;
select ename,eage,dense_rank() over(order by eage) from emp;

7. row_number() over() ：排序，无重复值，partition by 可选，order by 必选

select ename,eage,row_number() over(partition by job order by eage) from emp;
select ename,eage,row_number() over(order by eage) from emp;

8. first_value() over() ：取出分区中第一条记录的字段值，partition by 可选，order by 可选
   last_value() over() ：取出分区中最后一条记录的字段值，partition by 可选，order by 可选

select ename,first_value(salary) over() from emp;
select ename,first_value(salary) over(order by salary desc) from emp;
select ename,first_value(salary) over(partition by job) from emp;        
select ename,first_value(salary) over(partition by job order by salary desc) from emp;

9. first ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行
   last ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行

select job,max(salary) keep(dense_rank first order by salary desc),
max(salary) keep(dense_rank last order by salary desc) from emp
group by job;

10. lag() over() ：取出前n行数据，partition by 可选，order by 必选  lead() over() ：取出后n行数据，partition by 可选，order by 必选

select ename,eage,lag(eage,1,0) over(order by salary), 
lead(eage,1,0) over(order by salary) from emp;

select ename,eage,lag(eage,1) over(partition by esex order by salary),
lead(eage,1) over(partition by esex order by salary) from emp;