从rownum的SQL需求还能归纳出的知识

超、凢脫俗 2022-03-16 05:58 141阅读 0赞

这是杂货铺的第428篇文章

这两篇文章中，介绍了使用rownum的一个实际SQL需求，

《[从一条"错误"的SQL，了解rownum的作用][SQL_rownum]》

《[rownum的SQL需求][rownum_SQL]》

得到一些朋友们的反馈，发现还能再归纳出一些问题。

**问题1**：

针对如下这条SQL，使用了row\_number()函数，但外层使用了rownum，

![640?wx\_fmt=png][640_wx_fmt_png]

老虎刘老师指出，

> 这种写法又回到最初了，相当于没用分析函数。
> 
> 应该是分析函数生成的列给个别名，然后外层再用这个别名<=10，而不是还用rownum<=10。  
> 使用分析函数和rownum，两个逻辑不一样的SQL得到相同结果集，只能说是巧合。

归根结底，缺少对这个函数的理解，还是需要重新领悟下ROW\_NUMBER()，他的定义如下，

ROW_NUMBER() OVER ([query_partition_clause] order_by_clause)

ROW\_NUMBER()为查询出来的每一行记录生成一个序号，依次排序且不会重复，能用于实现top-N、bottom-N、inner-N，

> ROW\_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order\_by\_clause, beginning with 1.
> 
>  
> 
> This use of the function lets you implement top-N, bottom-N, and inner-N reporting

这是《Reference》的示例，可以看出department\_id相同的记录，别名是emp\_id，会标记序号，而且按照employee\_id的顺序进行排列，体现出ROW\_NUMBER()的作用，  
![640?wx\_fmt=png][640_wx_fmt_png 1]

这个则是实现inner-N，返回按照last\_name排序的51-100条记录，

![640?wx\_fmt=png][640_wx_fmt_png 2]

相应地，针对我们的需求，改为如下SQL，将ronwum<=10改为row\_number()<=10，而且注意个细节，即使内层查询，没有order by name desc将bbb排在aaa前面，此时使用t.c='aaa' OR t.c='bbb' AND t.e <= 10作为条件，并不会像《[从一条"错误"的SQL，了解rownum的作用][SQL_rownum]》中使用NAME='aaa' OR NAME='bbb' AND ROWNUM<=10作为条件，得到19条aaa和1条bbb，而是返回正确的19条aaa和10条bbb，

![640?wx\_fmt=png][640_wx_fmt_png 3]

**问题2**：

有一位朋友说，

> 我想通过union all来实现，一个取符合要求的aaa，一个是取bbb，最后一合并，可以，虽然有点慢。但是这样是没有顺序的，但是，当我要order by rowid时候，就报了错(ORA 01446)。

他用的SQL如下，

![640?wx\_fmt=png][640_wx_fmt_png 4]

ORA-01446的错误提示看，原因是无法从带distinct、group by的视图中检索ROWID，

> 01446, 00000, "cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc."  
> // \*Cause:  
> // \*Action:

我们从这条SQL，对应的执行计划，就能看出来，需要排序的是内层子查询视图的结果集，而视图的SELECT检索项中没有ROWID，所以根本不知道按照谁的ROWID进行排序，抛出错误ORA-01446，

![640?wx\_fmt=png][640_wx_fmt_png 5]

因此，只需要在子查询中的SELECT，增加ROWID字段，外层就能按照ROWID，进行排序，

![640?wx\_fmt=png][640_wx_fmt_png 6]

**问题3**：

将问题1和问题2，进行结合，还能解决name按照rowid排序的问题，

![640?wx\_fmt=png][640_wx_fmt_png 7]

对应执行计划，如下所示，

![640?wx\_fmt=png][640_wx_fmt_png 8]

P.S.

对于数据检索的顺序，多说一句，有时你看见的，未必是真相，可以参考《[Oracle读取数据的顺序问题][Oracle]》中对数据检索顺序的探索。

[SQL_rownum]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247488140&idx=1&sn=9ce237956dee95dca150a633dc706673&chksm=ea81577fddf6de69bc37b258848ba81a211045220e19e35aec19239e752319b235fee439bf6e&scene=21#wechat_redirect
[rownum_SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247488165&idx=1&sn=93660cc5da6b7fac89da20260e570106&chksm=ea815756ddf6de40bf0e5a9c7e50008a9dbd79f037564f7a46c63bfa8f6b46a1eacc20e40e78&scene=21#wechat_redirect
[640_wx_fmt_png]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PanlGDMym8PCvTvMVh5HRy0cqkXiaSd3oCLkzI59HUich71ku4PebYx6MQQb7Ara9daib142ibqH5gxZ5g/640?wx_fmt=png
[640_wx_fmt_png 1]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PamK1b3UJVL0xjNe5GxWxMImAoxqxhYxRYW2fbiabDVPWI9ibNmVpbJsBhe5yib8vfgaHahRuopUfQ8xA/640?wx_fmt=png
[640_wx_fmt_png 2]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PamK1b3UJVL0xjNe5GxWxMImic8IAdrWe9DVWxy48X9Sd0NcqCwCYQbEibXuuLzr9z7goS0pS0OwaRgA/640?wx_fmt=png
[640_wx_fmt_png 3]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PamK1b3UJVL0xjNe5GxWxMImibVOAss3vqH0SC7icaxGrNrESd8OOsL9WiaMxqs576iaCRJkADZ2CNps3g/640?wx_fmt=png
[640_wx_fmt_png 4]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PamK1b3UJVL0xjNe5GxWxMImSGj9CN7jHwhbRYwxIgAIS3JNR4Q0obpiaL6EuXcYQNFxBGyE272awPA/640?wx_fmt=png
[640_wx_fmt_png 5]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PamK1b3UJVL0xjNe5GxWxMImWb35IyH7HuJmhDp1rIk7AHAxIpa9lPYBTjEnvKFRkeZ3wdI3YLlibYA/640?wx_fmt=png
[640_wx_fmt_png 6]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PamK1b3UJVL0xjNe5GxWxMIm9Exjd7S4Lt8iaKiaBzF37ZGfIL72WV2PNE95EfAB6sfLiaXsnPWDet1rA/640?wx_fmt=png
[640_wx_fmt_png 7]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PamK1b3UJVL0xjNe5GxWxMImXLOPWXm5NqvjHOibHUFjAKNOgMITgiaTs1Z5xEJeiaEoBX3SYqEnLW3Mg/640?wx_fmt=png
[640_wx_fmt_png 8]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PamK1b3UJVL0xjNe5GxWxMImQTMicfqM56QklGPYUsial6tXr6rgdNaYNk2pgSia6wSRCxib10ia5pvzm4A/640?wx_fmt=png
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247487730&idx=1&sn=0f842be7a24f420e4d25646ff28786c1&chksm=ea815501ddf6dc17d90d4d51e917c190c07887f773fd1d2ac455a0e2d692f8c714588446bc2d&scene=21#wechat_redirect