sql SERVER进行最大值的记录进行获取 sql查询最大值记录

转载

mob64ca13fe1aa6 2024-08-14 11:23:55

`select * from (select t.*, row_number() over(partition by 分组字段 order by 排序字段 desc ) rn from tablename t ) where rn=1`

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).

　　与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．

　　row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．

　　rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

　　dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的．

　　lag（arg1,arg2,arg3):
arg1是从其他行返回的表达式
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。
arg3是在arg2表示的数目超出了分组的范围时返回的值。

看几个SQL语句:

语句一:

select row_number() over(order by sale/cnt desc) as sort, sale/cnt
 from (
 select -60 as sale,3 as cnt from dual union
 select 24 as sale,6 as cnt from dual union
 select 50 as sale,5 as cnt from dual union
 select -20 as sale,2 as cnt from dual union
 select 40 as sale,8 as cnt from dual); 执行结果:
           SORT       SALE/CNT
 ---------- ----------
              1             10
              2              5
              3              4
              4            -10
              5            -20

语句二:查询员工的工资,按部门排序

select ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order from scott.emp;
 执行结果:
 ENAME                           SAL      SAL_ORDER
 -------------------- ---------- ----------
 KING                           5000              1
 CLARK                          2450              2
 MILLER                         1300              3
 SCOTT                          3000              1
 FORD                           3000              2
 JONES                          2975              3
 ADAMS                          1100              4
 SMITH                           800              5
 BLAKE                          2850              1
 ALLEN                          1600              2
 TURNER                         1500              3
 WARD                           1250              4
 MARTIN                         1250              5
 JAMES                           950              6 已选择14行。
 语句三:查询每个部门的最高工资
 select deptno,ename,sal from
      (select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
          from scott.emp) where sal_order <2; 执行结果:
        DEPTNO ENAME                          SAL
 ---------- -------------------- ----------
            10 KING                          5000
            20 SCOTT                         3000
            30 BLAKE                         2850 已选择3行。

语句四：

select deptno,sal,rank() over (partition by deptno order by sal) as rank_order from scott.emp order by deptno;
 执行结果:
      DEPTNO         SAL RANK_ORDER
 ---------- ---------- ----------
          10        1300           1
          10        2450           2
          10        5000           3
          20         800           1
          20        1100           2
          20        2975           3
          20        3000           4
          20        3000           4
          30         950           1
          30        1250           2
          30        1250           2
          30        1500           4
          30        1600           5
          30        2850           6

已选择14行。

语句五：

select deptno,sal,dense_rank() over(partition by deptno order by sal) as dense_rank_order from scott.emp order by deptn; 
 
 执行结果：
      DEPTNO         SAL DENSE_RANK_ORDER
 ---------- ---------- ----------------
          10        1300                 1
          10        2450                 2
          10        5000                 3
          20         800                 1
          20        1100                 2
          20        2975                 3
          20        3000                 4
          20        3000                 4
          30         950                 1
          30        1250                 2
          30        1250                 2
          30        1500                 3
          30        1600                 4
          30        2850                 5

已选择14行。

语句六：

select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) as lag_ from scott.emp order by deptno;
 执行结果：
 
 
      DEPTNO ENAME                        SAL LAG_
 ---------- -------------------- ---------- --------------------
          10 CLARK                       2450
          10 KING                        5000 CLARK
          10 MILLER                      1300 KING
          20 ADAMS                       1100
          20 FORD                        3000 ADAMS
          20 JONES                       2975 FORD
          20 SCOTT                       3000 JONES
          20 SMITH                        800 SCOTT
          30 ALLEN                       1600
          30 BLAKE                       2850 ALLEN
          30 JAMES                        950 BLAKE
          30 MARTIN                      1250 JAMES
          30 TURNER                      1500 MARTIN
          30 WARD                        1250 TURNER

已选择14行。

`select * from (select t.*, row_number() over(partition by 分组字段 order by 排序字段 desc ) rn from tablename t ) where rn=1`

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).

　　与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．

　　row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．

　　rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

　　dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的．

看几个SQL语句:

语句一:

select row_number() over(order by sale/cnt desc) as sort, sale/cnt
 from (
 select -60 as sale,3 as cnt from dual union
 select 24 as sale,6 as cnt from dual union
 select 50 as sale,5 as cnt from dual union
 select -20 as sale,2 as cnt from dual union
 select 40 as sale,8 as cnt from dual); 执行结果:
           SORT       SALE/CNT
 ---------- ----------
              1             10
              2              5
              3              4
              4            -10
              5            -20 
 语句二:查询员工的工资,按部门排序
 select ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order from scott.emp;

执行结果:

ENAME                           SAL      SAL_ORDER
 -------------------- ---------- ----------
 KING                           5000              1
 CLARK                          2450              2
 MILLER                         1300              3
 SCOTT                          3000              1
 FORD                           3000              2
 JONES                          2975              3
 ADAMS                          1100              4
 SMITH                           800              5
 BLAKE                          2850              1
 ALLEN                          1600              2
 TURNER                         1500              3
 WARD                           1250              4
 MARTIN                         1250              5
 JAMES                           950              6 已选择14行。
 语句三:查询每个部门的最高工资
 select deptno,ename,sal from
      (select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
          from scott.emp) where sal_order <2; 执行结果:
        DEPTNO ENAME                          SAL
 ---------- -------------------- ----------
            10 KING                          5000
            20 SCOTT                         3000
            30 BLAKE                         2850

已选择3行。

语句四：

select deptno,sal,rank() over (partition by deptno order by sal) as rank_order from scott.emp order by deptno;
 执行结果:
      DEPTNO         SAL RANK_ORDER
 ---------- ---------- ----------
          10        1300           1
          10        2450           2
          10        5000           3
          20         800           1
          20        1100           2
          20        2975           3
          20        3000           4
          20        3000           4
          30         950           1
          30        1250           2
          30        1250           2
          30        1500           4
          30        1600           5
          30        2850           6

已选择14行。

语句五：

select deptno,sal,dense_rank() over(partition by deptno order by sal) as dense_rank_order from scott.emp order by deptn; 
 
 执行结果：
      DEPTNO         SAL DENSE_RANK_ORDER
 ---------- ---------- ----------------
          10        1300                 1
          10        2450                 2
          10        5000                 3
          20         800                 1
          20        1100                 2
          20        2975                 3
          20        3000                 4
          20        3000                 4
          30         950                 1
          30        1250                 2
          30        1250                 2
          30        1500                 3
          30        1600                 4
          30        2850                 5

已选择14行。

语句六：

select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) as lag_ from scott.emp order by deptno;
 执行结果：
 
 
      DEPTNO ENAME                        SAL LAG_
 ---------- -------------------- ---------- --------------------
          10 CLARK                       2450
          10 KING                        5000 CLARK
          10 MILLER                      1300 KING
          20 ADAMS                       1100
          20 FORD                        3000 ADAMS
          20 JONES                       2975 FORD
          20 SCOTT                       3000 JONES
          20 SMITH                        800 SCOTT
          30 ALLEN                       1600
          30 BLAKE                       2850 ALLEN
          30 JAMES                        950 BLAKE
          30 MARTIN                      1250 JAMES
          30 TURNER                      1500 MARTIN
          30 WARD                        1250 TURNER

已选择14行。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。