前两天同事发现网站上同一个商品在几页里都出现,然后查语句,发现有诡异的事情,原来以为可能是物化视图的关系,不过今天我测试了下,是和ORDER BY 里出现并列数据有关
先举例说明下
SELECT a.*,rownum
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a
出来的结果
加了过滤
SELECT *
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc)
WHERE ROWNUM <9
另外种写法
SELECT *
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc)
WHERE ROWNUM <= 8
可以看到6-8项目不一样了,按道理rownum<=8和rownum<9结果是一样,但出现了不同,不过如果再包层,结果就一致了,并且是正确的
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a)
WHERE rn < 9
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a)
WHERE rn <=8
先举例说明下
SELECT a.*,rownum
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a
出来的结果
加了过滤
SELECT *
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc)
WHERE ROWNUM <9
另外种写法
SELECT *
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc)
WHERE ROWNUM <= 8
可以看到6-8项目不一样了,按道理rownum<=8和rownum<9结果是一样,但出现了不同,不过如果再包层,结果就一致了,并且是正确的
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a)
WHERE rn < 9
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a)
WHERE rn <=8
解决方案 »
- 创建表空间为什么使用的时候报错呢
- 求助:如何查询教师表,同时显示 教师姓名+教师的学生的个数
- 如何实现oracle数据库间的差异同步?
- 这样的SQL如何让其按原来的顺序排序?
- 问一个非技术方面的问题,现在很混乱,请帮我看看吧。
- 我建的DBLINK为什么总是报”ORA-12154: TNS: 无法处理服务名“的错呢?代码如下:
- 用PL/SQL导出用户表的问题
- 怎么才能知道一个用户是否存在于库表中呢
- Oracle Management Server啟動問題
- 请问怎么在ado对象里访问dbms_output.put_line('kkkkkkkkkkkk')输出的值?
- Oracle 语法问题
- 请教一个循环输出方面的问题
可能和子查询在内存中的排序有关吧
值相同时,排序不稳定。执行相同查询的时候,会从共享池中调用之前的结果
因为调用了相同的数据集
SELECT a.*, ROWNUM rn
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a
因此rn<=8和rn<9是一样的至于前面的写法,
SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc
order by会在内存中重新排序?因此2次排序结果不一定相同
这是我的猜测
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc)
WHERE ROWNUM <9这里的rownum与嵌套内层的rownum不相同,外层的rownum与嵌套内层的rownum没有任何关系
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a)
WHERE rn < 9
会不会是因为上面这个查询比下面这个查询多了一层查询 也就多了一次排序所导致的结果
SELECT *
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc)
WHERE ROWNUM <9
SELECT *
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc)
WHERE ROWNUM <9
红色部分查询出来的结果是经过ORDER BY 是有序的
所以结果就是从有序的结果集合中取出前9条再看看另外一个
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM ([color=#FF0000]SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a[/color])
WHERE rn < 9
红色部分出来的结果是有序的 可是经过绿色的一个查询 结果还一定保持有序吗
如果已经不是有序的话 那么外层再选出的前9条可能也就不是刚才的前9条了
FROM (SELECT ROWNUM rn, good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a
WHERE rn < 9这样就行
在内层查询中把数据按顺序附上了序号,在外层引用的时候可以直接当成实际存在的列来用
FROM (SELECT good_id, good_nm, prc,rownum rn
FROM tb_ag001
ORDER BY prc)
WHERE rn<9
和
SELECT *
FROM (SELECT good_id, good_nm, prc,rownum rn
FROM tb_ag001
ORDER BY prc) WHERE rn<=8
这两个结果出来的是一样的,但不是我想要的结果
如下:
SELECT *
FROM
(
SELECT a.*, ROWNUM rn
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc --第一层,这是where条件层,这是必须的,这个时候取的是基本数据。
) a --第二层,在原来的结果集上进行列表,排需要,记录所有的rownum,以便于分页的时候取数据
)b --第三层,真正的分页取数据层,想取第几页就取第几页。
WHERE b.rn < 9当然,如果你没有这个思路逻辑的话,可以用分析函数来列记录行,这样就不会乱了,这个只需要2层就搞定了,如下:select a.*
from(
SELECT good_id, good_nm, prc
row_num() over(order by prc) rn--排序集合行号,这种排序的话,你外面是<9或者<=8结果都是一样的,而且这种排序效率比上一种还要高的。
FROM tb_ag001
)a where a.rn<9【注释】:建议大家排序的时候,采用分析函数的行号,来进行排序分页,这样效率高些。
呵呵,我觉得你特别迷分析函数,分析函数不是万能的,因为里面也有ORDER BY,所以也会消耗不少时间,
其实对于这种语句用分析函数并不比rownum+order by 快,因为对大家来说是一样的要ORDER BY一下
分析函数比较适用于需要复杂计算的,可能需要多次自关联才能出来的环境,这样的分析函数的优势才会体现出来
select *
from (
select a.*
from a
order by prc
)
where rownum<=8select *
from (
select a.*,row_number() over(order by prc) rn
from a
)
where rn<=8这两句的结果及效率应该是一样的
实际上用row_number一样会出现以上的问题
SELECT *
FROM (SELECT good_id, good_nm, prc, ROW_NUMBER () OVER (ORDER BY prc) rn
FROM tb_ag001)
WHERE rn <= 8SELECT *
FROM (SELECT good_id, good_nm, prc, ROW_NUMBER () OVER (ORDER BY prc) rn
FROM tb_ag001)
WHERE rn <9
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc)
WHERE ROWNUM <9这句有问题的关键是过滤条件WHERE ROWNUM < 9造成的这和ORDER BY没有太直接的联系,关键是ROWNUM的问题。
------ ------------------------------ ---------- ----------
133337 阳光宝贝防晒修复套SPF-30 0 1
133424 旋覆透白绣花毛巾X1 0 2
133428 左旋C新纪润白霜(7ml) 0 3
133430 左旋C美白保湿精乳 0 4
133528 美白晶澈爽肤水 0 5
133530 左旋C燕窝初雪调养面膜 0 6
133628 左旋C-燕窝初雪调养面膜8g*2 0 7
133728 化妆包1个 0 8
133828 BICATO活菌酵母修护晚霜5g 0 9
138068 蓝牙耳机*1128TF卡*1 0 10
138069 超速读卡器*1 0 11
138073 内置128兆TF卡天然水晶手链*1 0 12
138074 内置128兆TF卡水晶手链 0 13已选择13行。已用时间: 00: 00: 00.00
16:30:06 scott@TUNGKONG> SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT good_id, good_nm, prc FROM tb_ag001 ORDER BY prc) a) WHERE rn < 9;GOOD_I GOOD_NM PRC RN
------ ------------------------------ ---------- ----------
133337 阳光宝贝防晒修复套SPF-30 0 1
133424 旋覆透白绣花毛巾X1 0 2
133428 左旋C新纪润白霜(7ml) 0 3
133430 左旋C美白保湿精乳 0 4
133528 美白晶澈爽肤水 0 5
133530 左旋C燕窝初雪调养面膜 0 6
133628 左旋C-燕窝初雪调养面膜8g*2 0 7
133728 化妆包1个 0 8已选择8行。 -----------------这是正确的结果,因为后面的过滤条件用的是rn < 9。已用时间: 00: 00: 00.01
16:30:21 scott@TUNGKONG> SELECT * FROM (SELECT good_id, good_nm, prc FROM tb_ag001 ORDER BY prc) WHERE ROWNUM < 9;GOOD_I GOOD_NM PRC
------ ------------------------------ ----------
133337 阳光宝贝防晒修复套SPF-30 0
133424 旋覆透白绣花毛巾X1 0
133428 左旋C新纪润白霜(7ml) 0
133430 左旋C美白保湿精乳 0
133528 美白晶澈爽肤水 0
133530 左旋C燕窝初雪调养面膜 0
138074 内置128兆TF卡水晶手链 0
133728 化妆包1个 0已选择8行。 --------这是错误的结果。区别在于133628和138074这两条记录。已用时间: 00: 00: 00.01
16:30:46 scott@TUNGKONG> SELECT rownum,a.* from tb_ag001 a order by prc; ROWNUM GOOD_I GOOD_NM PRC
---------- ------ ------------------------------ ----------
1 133337 阳光宝贝防晒修复套SPF-30 0
2 133424 旋覆透白绣花毛巾X1 0
3 133428 左旋C新纪润白霜(7ml) 0
4 133430 左旋C美白保湿精乳 0
5 133528 美白晶澈爽肤水 0
6 133530 左旋C燕窝初雪调养面膜 0
13 133628 左旋C-燕窝初雪调养面膜8g*2 0 --------------
8 133728 化妆包1个 0
9 133828 BICATO活菌酵母修护晚霜5g 0
10 138068 蓝牙耳机*1128TF卡*1 0
11 138069 超速读卡器*1 0
12 138073 内置128兆TF卡天然水晶手链*1 0
7 138074 内置128兆TF卡水晶手链 0 --------------已选择13行。已用时间: 00: 00: 00.03
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a)
WHERE rn < 9SELECT *
FROM (SELECT ROWNUM rn, good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a
WHERE rn < 9
这两句sql查询出的数据是一致的,要想顺序也一致,只有表的数据时按照prc从小到大insert进去才行,否则得话第二句最后还得加个ORDER BY prc,不过加上order后,执行速度肯定受影响
在最外层用rownum做条件是肯定不行的,因为rownum是在当前查询检索完之后才产生的,所以用它做条件,每次检索出来的结果随即性比较大
SELECT * FROM (SELECT good_id, good_nm, prc FROM tb_ag001 ORDER BY prc) WHERE ROWNUM < 9;执行这句的时候,ROWNUM < 9这个条件会把子查询中的数据过滤,子查询选出的数据是含138074的数据,所以最终的结果也就是含138074的数据了。
这两句的结果是不一致的。。第二句中产生的rn和第一句是不一致的,这个时候的rn已经等同于错误结果的rownum的效果了
则是先ROWNUM排序,再ORDER BY
而语句的目地是为了得到排序后的结果后再分页
那么
select a.* from
(select * from test order by id) a;
查询出来的结果是有序的还是无序的呢
上面我贴出来结果的
索引是在GOOD_ID上,这个没有重复的估计可能和并列排序情况下顺序读取数据有关吧
实在不行,就是在order by prc 后面再加上主键id好了,order by prc asc, id desc等等(最新的排在前面,所以主键id desc)。
如果要得出个稳定的结果,须在最里层用个其它的字段固定住(如将rownum取出做为个字段),然后外层在进行加条件。