Shulou(Shulou.com)05/31 Report--

This article is about how to use the rownum keyword to query employee information in oracle. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

Note: for rownum (pseudo columns) you can only use the

Will not be able to return any data

Ex.: inquire the name, salary and salary of the top 20 employees from high to low

Select rownum,first_name,salary from (Select first_name,salary from employees Group by salary desc) Where rownum==,=,between...and, can only use the above symbols (10 (if you write such a query statement, you should want to get the last 10 records in the table), you will find that the displayed results will disappoint you, and you may wonder who deleted some records, and then check the number of records. Is it still 20? So what's the problem?

Let's first understand the meaning of rownum. Because ROWNUM is a pseudo column added to the result set, that is, a column that is first found and then added (emphasis: there must be a result set first). To put it simply, rownum is the sequence number of the result that matches the condition. It always starts at 1. So it is impossible for you to choose a result without 1, but with other values greater than 1. So you can't expect the following result set:

11 aaaaaaaa

12 bbbbbbb

13 ccccccc

.

Rownum > 10 is not recorded, because if the first item is not satisfied to be removed, the ROWNUM of the second item becomes 1, so there is never a record that meets the condition. Or it can be understood like this:

A ROWNUM is a sequence that is the order in which an oracle database reads data from a data file or buffer. When it gets the first record, the rownum value is 1, the second is 2, and so on. If you use the conditions >, > =, =, between...and, because the rownum of the first record obtained from the buffer or data file is 1, it is deleted, and then the next bar is taken, but its rownum is still 1, is deleted, and so on, there is no data.

With the above concept of rownum established from different aspects, we can understand several phenomena of using rownum.

1. Why does select rownum,c1 from T1 where rownum! = 10 return the first 9 pieces of data? It is similar to select rownum,c1 from tablename where rownum.

= 10, so only the first nine records are displayed. It can also be understood that the rownum of the record after rownum is 9 is 10, so it is removed because the condition is! = 10, and then the record is added, and the rownum is 10, which is also removed. If you go down, you will only show the first nine records.

two。 Why can't a single record be found when rownum > 1, while rownum > 0 or rownum > = 1 always shows all records?

Because rownum is added after the result set of the query, it always starts with 1

3. Why can between 1 and 10 or between 0 and 10 find the result, but not with between 2 and 10?

The reason is the same as above, because rownum always starts with 1.

As can be seen from the above, it is wrong to discard the rownum=1 record at any time. It is indispensable in the result set. Without rownum=1, it cannot exist like a castle in the air, so your rownum condition should be included to 1.

But if you want to use the condition rownum > 10, you should use a nested statement to make Mr. rownum, and then query him.

Select * from (selet rownum as rn,t1.* from a where...) Where rn > 10

The above is how to use the rownum keyword in oracle to query employee information. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.