返回最大值所在行的几种方式比较

最新推荐文章于 2022-09-26 00:19:44 发布
最新推荐文章于 2022-09-26 00:19:44 发布
阅读量3.2k 收藏
点赞数
分类专栏： 改写优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jgmydsai/article/details/18358115
版权

本例用dba_objects建表，因表大小不一样，与你的结果可能也不一样，如要参考时需要实测

建立测试表如下

SQL> create table test1 as select * from dba_objects;
Table created

SQL> create index idx_test1 on test1(owner,object_name);
Index created

SQL> alter table test1 modify owner not null;
Table altered
SQL> select count(*) from test1;
  COUNT(*)
----------
     87034

用keep语句，这种方法object_name不能有重复值 

SELECT *
  FROM test1 a
 INNER JOIN (SELECT MAX(b.rowid) keep(dense_rank LAST ORDER BY object_name) AS rid
               FROM test1 b
              GROUP BY b.owner) b
    ON b.rid = a.rowid;

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		| Name	    | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	|	    |	   1 |	      |       |       |  1937 (100)|	      |     30 |00:00:07.31 |	 1764 |       |       | 	 |
|*  1 |  HASH JOIN		|	    |	   1 |	   95M|    20G|  2296K|  1937  (14)| 00:00:24 |     30 |00:00:07.31 |	 1764 |  1645K|  1645K| 1529K (0)|
|   2 |   VIEW			|	    |	   1 |	97907 |  1147K|       |   147	(3)| 00:00:02 |     30 |00:00:02.66 |	  514 |       |       | 	 |
|   3 |    SORT GROUP BY	|	    |	   1 |	97907 |  9083K|       |   147	(3)| 00:00:02 |     30 |00:00:02.66 |	  514 |  9216 |  9216 | 8192  (0)|
|   4 |     INDEX FAST FULL SCAN| IDX_TEST1 |	   1 |	97907 |  9083K|       |   144	(0)| 00:00:02 |  87034 |00:00:01.23 |	  514 |       |       | 	 |
|   5 |   TABLE ACCESS FULL	| TEST1     |	   1 |	97907 |    20M|       |   347	(1)| 00:00:05 |  87034 |00:00:01.18 |	 1250 |       |       | 	 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

用row_number返回rowid后再自关联 

SELECT /*+ use_nl(b,a)*/ *
  FROM test1 a
  INNER JOIN (SELECT ROWNUM AS sn,
                    rid
                     FROM (SELECT ROWID AS rid,
                                  row_number() over(PARTITION BY owner ORDER BY object_name) AS seq
                             FROM test1)
                    WHERE seq = 1) b
                    ON b.rid = a.rowid;


--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	| Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time	| Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		|      1 |	  |	  | 98456 (100)|	  |	30 |00:00:07.15 |     537 |	  |	  |	     |
|   1 |  NESTED LOOPS		    |		|      1 |  97907 |    22M| 98456   (1)| 00:19:42 |	30 |00:00:07.15 |     537 |	  |	  |	     |
|   2 |   VIEW			    |		|      1 |  97907 |  2390K|   529   (1)| 00:00:07 |	30 |00:00:07.14 |     509 |	  |	  |	     |
|   3 |    COUNT		    |		|      1 |	  |	  |	       |	  |	30 |00:00:07.14 |     509 |	  |	  |	     |
|*  4 |     VIEW		    |		|      1 |  97907 |  2390K|   529   (1)| 00:00:07 |	30 |00:00:07.14 |     509 |	  |	  |	     |
|*  5 |      WINDOW NOSORT	    |		|      1 |  97907 |  9083K|   529   (1)| 00:00:07 |  87034 |00:00:05.91 |     509 |	9M|  1232K|	     |
|   6 |       INDEX FULL SCAN	    | IDX_TEST1 |      1 |  97907 |  9083K|   529   (1)| 00:00:07 |  87034 |00:00:02.33 |     509 |	  |	  |	     |
|   7 |   TABLE ACCESS BY USER ROWID| TEST1	|     30 |	1 |   219 |	1   (0)| 00:00:01 |	30 |00:00:00.01 |      28 |	  |	  |	     |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

用两个列自关联并强制走索引 

SELECT /*+ index(a,idx_test1) */
 a.*
  FROM test1 a
 WHERE (a.owner, a.object_name) IN
       (SELECT b.owner, MAX(b.object_name) FROM test1 b GROUP BY b.owner);

Elapsed: 00:00:05.24

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name	 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |	A-Time	 | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		 |	1 |	   |	   |  2041 (100)|	   |	 31 |00:00:02.45 |     611 |	  1 |	    |	    |	       |
|   1 |  NESTED LOOPS		     |		 |	1 |	 1 |   290 |  2041   (1)| 00:00:25 |	 31 |00:00:02.45 |     611 |	  1 |	    |	    |	       |
|   2 |   NESTED LOOPS		     |		 |	1 |	 1 |   290 |  2041   (1)| 00:00:25 |	 31 |00:00:02.44 |     580 |	  0 |	    |	    |	       |
|   3 |    VIEW 		     | VW_NSO_1  |	1 |  97907 |  7935K|   147   (3)| 00:00:02 |	 30 |00:00:02.43 |     514 |	  0 |	    |	    |	       |
|   4 |     HASH GROUP BY	     |		 |	1 |	 1 |  7935K|   147   (3)| 00:00:02 |	 30 |00:00:02.43 |     514 |	  0 |	 17M|  4248K| 2249K (0)|
|   5 |      INDEX FAST FULL SCAN    | IDX_TEST1 |	1 |  97907 |  7935K|   144   (0)| 00:00:02 |  87034 |00:00:01.14 |     514 |	  0 |	    |	    |	       |
|*  6 |    INDEX RANGE SCAN	     | IDX_TEST1 |     30 |	 1 |	   |	 2   (0)| 00:00:01 |	 31 |00:00:00.01 |	66 |	  0 |	    |	    |	       |
|   7 |   TABLE ACCESS BY INDEX ROWID| TEST1	 |     31 |	 1 |   207 |	 3   (0)| 00:00:01 |	 31 |00:00:00.01 |	31 |	  1 |	    |	    |	       |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------


用object_name关联max(object_name) 

SELECT /*+ index(a,idx_test1) */
 a.*
  FROM test1 a
 WHERE a.object_name =
       (SELECT MAX(b.object_name) FROM test1 b WHERE b.owner = a.owner);

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name	 | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |	A-Time	 | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		 |	1 |	   |	   |	   | 67184 (100)|	   |	 31 |00:00:12.82 |   66062 |	 18 |	    |	    |	       |
|*  1 |  HASH JOIN		     |		 |	1 |    144K|	39M|  9088K| 67184   (1)| 00:13:27 |	 31 |00:00:12.82 |   66062 |	 18 |  1229K|  1229K| 1908K (0)|
|   2 |   VIEW			     | VW_SQ_1	 |	1 |  97907 |  7935K|	   |   147   (3)| 00:00:02 |	 30 |00:00:02.67 |     514 |	  0 |	    |	    |	       |
|   3 |    HASH GROUP BY	     |		 |	1 |  97907 |  7935K|	   |   147   (3)| 00:00:02 |	 30 |00:00:02.67 |     514 |	  0 |	 17M|  4248K| 3145K (0)|
|   4 |     INDEX FAST FULL SCAN     | IDX_TEST1 |	1 |  97907 |  7935K|	   |   144   (0)| 00:00:02 |  87034 |00:00:01.21 |     514 |	  0 |	    |	    |	       |
|   5 |   TABLE ACCESS BY INDEX ROWID| TEST1	 |	1 |  97907 |	19M|	   | 65581   (1)| 00:13:07 |  87034 |00:00:06.49 |   65548 |	 18 |	    |	    |	       |
|   6 |    INDEX FULL SCAN	     | IDX_TEST1 |	1 |  97907 |	   |	   |   529   (1)| 00:00:07 |  87034 |00:00:01.30 |     509 |	  0 |	    |	    |	       |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


用row_number()返回各组第一行 

SELECT * FROM
(
SELECT test1.*,
       row_number() over(PARTITION BY owner ORDER BY object_name) AS seq
  FROM test1
)
WHERE seq = 1;

---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		 | Name  | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |	A-Time	 | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	 |	 |	1 |	   |	   |	   |  4751 (100)|	   |	 30 |00:00:03.40 |    1248 |	   |	   |	      |
|*  1 |  VIEW			 |	 |	1 |  97907 |	20M|	   |  4751   (1)| 00:00:58 |	 30 |00:00:03.40 |    1248 |	   |	   |	      |
|*  2 |   WINDOW SORT PUSHED RANK|	 |	1 |  97907 |	19M|	23M|  4751   (1)| 00:00:58 |	 65 |00:00:03.39 |    1248 |	14M|  1437K|   12M (0)|
|   3 |    TABLE ACCESS FULL	 | TEST1 |	1 |  97907 |	19M|	   |   347   (1)| 00:00:05 |  87034 |00:00:01.26 |    1248 |	   |	   |	      |
---------------------------------------------------------------------------------------------------------------------------------------------------------------


jgmydsai
关注
专栏目录
C语言实现最大间隙问题实例
09-04
在分配数据的过程中，我们更新每个区间的左右边界，以确保它们始终包含当前区间内的最小值和最大值。之后，通过比较相邻非空区间之间的边界差值，我们可以找到最大间隙。这里的关键在于，由于区间总数为n-1，但实数...
初探oracle删除重复记录，只保留rowid最小的记录
qq_39081511的博客
09-12 939
如题，初探oracle删除重复记录，只保留rowid最小的记录（rowid可以反映数据插入到数据库中的顺序） 一、删除重复记录可以使用多种方法，如下只是介绍了两种方法(exist和in两种)。 1.首先创建一个测试表。 create table my_users( id number, username varchar2(20), sal number ) 2.插...
plsql删除大量数据_oracle 快速删除大批量数据方法（全部删除，条件删除，删除大量重复记录）...
weixin_33488806的博客
02-28 3212
全部删除如果是删除某个表的所有数据，并且不需要回滚，使用 TRUNCATE 就ok了。SQL> truncate table table_name;条件删除如果删除数据有条件，如 delete from tablename where col1 = 'lucy';这时除了加索引外, 你可以删除时加NO LOGGING选项,不写日志加快删除速度引用某人的一句话“几千万条记录的表都不...
sql如何找到某字段的最大值所在行
翔云
03-12 5万+
首先，查找某字段的最大值select max(weight) from apple; 7888接着，根据最大值，查找其所在行select * from apple where weight =7888;
获取数据表分组的最大值所在行所有信息的方法
dreamfly88的专栏
08-12 4682
业务系统中经常会有一些统计，比如对商品销量和销售额进行汇总统计，并且获取销量最大的商品，是哪个业务员销售，或则哪个月份最高等等，这样就需要明确知道最大值所在行或者ID，这里提供两种方法进行统计: 首先创建基础数据，如下: DROP TABLE orders; CREATE TABLE orders (id VARCHAR(10), statu CHAR(1), goods_id VARCH
数据结构几种排序
03-15
- `partition`函数用于实现分区操作，返回基准值所在的位置。 #### 四、归并排序（Merge Sort） **定义与原理：** 归并排序是建立在归并操作上的一种有效的排序算法，效率为O(n log n)。该算法是采用分治法的一个...
mysql分组取每组前几条记录(排名) 附group by与order by的研究
09-11
例如，如果我们想要获取每个`name`分组中`val`字段值最大的记录，可以采用以下几种方法： 1. 子查询法： ```sql SELECT a.* FROM tb a WHERE val = (SELECT MAX(val) FROM tb WHERE name = a.name) ORDER BY a...
求二叉树节点的最大距离
04-07
在计算机科学中，二叉树是一种非常重要的数据结构，而求二叉树节点的最大距离是二叉树中一个经典的问题。问题的定义是这样的：在二叉树中，计算任意两个节点之间的最大路径长度，路径长度是指路径上的边数。解决这个...
.Net性能优化的几点建议
10-20
.NET中的对象分为引用类型和值类型两种，它们在内存中的存储方式不同，因此对性能的影响也不同。 - **引用类型**：默认情况下，引用类型的对象分配在托管堆上。当创建一个引用类型对象时，会在堆上为其分配内存，并...
Python 实现Numpy中找出array中最大值所对应的行和列
01-02
Python特别灵活，肯定方法不止一种，这里介绍一种我觉得比较简单的方法。 如下图，使用x == np.max(x) 获得一个掩模矩阵，然后使用where方法即可返回最大值对应的行和列。 where返回一个长度为2的元组，第一个元素保存的是行号，第二个元素保存的是列号。 以上这篇Python 实现Numpy中找出array中最大值所对应的行和列就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持软件开发网。 您可能感兴趣的文章:浅谈Python3 numpy.ptp()最大值与最小值的差python 判断三个数字中的最大值实
VBA代码拿到一列中最大值所在的一行
05-23
VBA源代码，根据某列中的最大值，拿到该行的行号。此代码为取出若干小区的信息，7*24小时，拿到某列值最大的行号，精简成7行
Python DataFrame：提取某字段最大值所对应的行
AI数据工厂
09-04 5万+
我们处理某些表格数据时，会遇到某个字段在不同类别中有不同的值，有时候需要提取某个类别中该字段最大值所对应的行，只用group by 往往不行。其实，使用group by 和 merge 相结合，即可轻松解决这个问题。 解决思路： 1、我们先来定义一个DataFrame： 在上图的df中，不同的name对应着num值、年份，现在想提取每一year的最大num所对应的name。 2、接下来...
SQL查询最大值，返回整行数据
热门推荐
寒泉
06-21 14万+
SQL查询最大值，返回整行数据 1、问题 部分数据如下，未完整展示。如何从 table_a 表中查询 p_postions 列的最大值对应的一行数据？ 在这里插入图片描述 2、解答 方法1：先排序，再取第一条 SELECT * FROM table_a order by p_postions desc limit 1; 查询结果如下： 在这里插入图片描述 经过评论区小伙伴的提醒，这种先排序的方式虽然语法简单，但时间复杂度较高，是 O(n*logn)。 方法2：先查最大值，再找到对应行
oracle中rowid的用法
木头
02-10 1万+
rowid概述 rowid是一个用来唯一标记表中行的伪列。它是物理表中行数据的内部地址，包含两个地址，其一为指向数据表中包含该行的块所存放数据文件的地址，另一个是可以直接定位到数据行自身的这一行在数据块中的地址。 除了在同一聚簇中可能不唯一外，每条记录的rowid是唯一的。可以理解成rowid就是唯一的。 扩展ROWID 从Oracle 8i开始使用扩展rowid标识行物理地址 扩展rowid使用base64编码行的物理地址，编码字符包含A-Z, a-z, 0-9, +,和/。 扩展rowid.
比较两个最大值，返回最大值
最新发布
y2276152767的博客
09-26 454
看看得了
matlab几种关于如何求矩阵最大值并返回其行列号的方法
Violeteee的博客
08-08 7302
第一种： clear;clc; A=[0 17 50;-12 40 3;5 -10 2;30 4 3] [C,I]=max(A(????) [m,n]=ind2sub(size(A),I) 第二种： clear;clc; A=[0 17 50;-12 40 3;5 -10 2;30 4 3] [M,I]=max(A) [N,J]=max(M) [I(J),J] 第三种： clear;clc; A=[0 17 50;-12 40 3;5 -10 2;30 4 3] N=max(max(A)) %或者N=m
MySQL查询最大（小）值所在行，查询分组后前N行
liangwenrong的博客
06-29 5289
没有开窗函数over()时，纯sql查询最大值行，主要思路归纳为以下几种： · 思路1、使用group by分组，因为分组后只会返回每组的第一行记录，所以分组前先排序，把需要的行记录排到分组的第一行，最后分组可得每组第一行数据。 · 思路2、一个表自己跟自己left join，在每个分组内，假设按age排序，则通过a.age<b.age连接后，每个a.age都会关联一组比自己大的b.age，最后统计a.age的关联数count(a.age)，0表示没有b.age比自己大（a.age为最大值），1表示.
MySQL查询最大值、最小值所在行
过河的小卒子的博客
09-06 7915
SQL查询最大值所在行
SQL查询技巧：获取分组后最大值的记录
外部查询的WHERE子句比较当前行的值与子查询返回的最大值，确保返回的是最大值所在行。 ```sql select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name ``` 2. 方法...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值