【SQL】——Oracle之ROWNUM

    这次面试，经理问了这样一个问题：

    有一张表，id不连续，我如何查出第3行至第5的数据，用的mysql数据库。
    因为在医药进销存系统中做过分页，用的oracle，写过查询第几行至第几行的sql。所以直接就说了，数据库有rownum。sql语句为：

<span style="font-family:KaiTi_GB2312;font-size:18px;">SELECT
	rn,
	user_id
FROM
	(
		SELECT
			ROWNUM rn,
			user_id,
			USER_NAME
		FROM
			t_user
		WHERE
			ROWNUM <= 5
	)
WHERE
	rn >= 3</span>

    还算比较顺利，但回来查了，我有很多没考虑到。

    1.rownum只是oracle中有。

    2.rownum还有很多需要注意的地方。记得DRP中的分页查询强调了3层嵌套。源码是：

<span style="font-family:KaiTi_GB2312;font-size:18px;">SELECT
	user_id,
	user_name,
	PASSWORD,
	contact_tel,
	email,
	create_date
FROM
	(
		SELECT
			ROWNUM rn,
			user_id,
			user_name,
			PASSWORD,
			contact_tel,
			email,
			create_date
		FROM
			(
				SELECT
					user_id,
					user_name,
					PASSWORD,
					contact_tel,
					email,
					create_date
				FROM
					t_user
				WHERE
					USER_ID <> 'root'
				ORDER BY
					USER_ID
			)
		WHERE
			ROWNUM <= 5
	)
WHERE
	rn > = 2</span>

    我当时就想，为什么不直接这样写 ，只用一层：

<span style="font-family:KaiTi_GB2312;font-size:18px;">SELECT
	ROWNUM,
	user_id,
	user_name,
	PASSWORD,
	contact_tel,
	email,
	create_date
FROM
	t_user
WHERE
	USER_ID <> 'root'
ORDER BY
	USER_ID
WHERE
	ROWNUM <= 5 and ROWNUM>=2

</span>

    通过这次对比总结，我现在明白为什么了。

    原来rownum有一些需要注意的地方，如：

    第二层存在的必要性：

    若不分开两层，即取rownum同时order by。我们设想的是，sql会先把数据按id排序，然后给排好序的记录编号，但事实会这样：如：

select rownum ,id,name from student order by name;

结果会是：

   ROWNUM ID     NAME

---------- ---------------------------------------------------------

        3 200003 李三

        2 200002 王二

        1 200001 张一

        4 200004 赵四

    1.即，sql先给记录编号，然后排序。这样我们按rownum查询数据只能是排序前的。所以 一二层必须分开 ，这样就能保证先排序，后排号。

    然后是第三层存在的必要性：

    下面是我查到的rownum需要注意的；

    rownum对于大于某值的查询条件：

    2.如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。

    SQL>select* from(select rownum no ,id,name from student) where no>2;  --有记录

    SQL>select * from(select rownum,id,name from student)where rownum>2; --无记录

    所以在第二层直接查rownum<n （n>=2），是查不到数据的。

    所以三层确实都是要存在的，第一层排序，第二层编号并具体查询的结束行号，第三层具体查询的起始行号。

    另附上参考网址：点击打开链接

   

    思考：

    刚卫中提到了，按这种查询，如果查第十万至十万零五的数据，它首先查询并排序，然后查出前十万零五条数据，然后再查询出十万后的数据，性能会受影响吧？

    卫中已做出部分实验，在sqlserver中根据row_number排号，并查询betweent某两行号之间的数据，查询百条数据时间为0ms级别。我将在Oracle对rownum进行实验，然后将对比结果告诉大家，so,结果请看下篇......