[ Summary ]

Just touched oracle My classmates may often be rowid and rownum These two words are confused , Make sure these two guys are good for us to write sql It would be very helpful , Now I'll throw a brick to attract jade , Let's talk about the difference between them .

[ Compare ]

rowid and rownum All are oracle Pseudo columns in , But there are essential differences between them :

rowid: It's the physical address , Used to locate data in a data table , It's unique and it won't change .

rownum: It is a logical number assigned to each row according to the result set of the query , The query results are different , rownum Nature is different .

For the same record , Different query conditions , rownum Would be different , But rowid Will not change .

Demo sample : Check all the employees of the company

select rowid, rownum, empno, ename from emp;

Demo sample : Check the name of the company's employees, including 'S'

select rowid, rownum, empno, ename from emp where ename like '%S%';

It's not hard for careful students to find out , For one line of records , rowid It hasn't changed , rownum Changed . such as : The name is 'SMITH' The employees' .

[ trap ]

1. rownum Only symbols are supported <、<=、!=, I won't support it >, >=, =, between...and

select * from emp where rownum > 10 -- No results 
select *
from (select rownum row_num, e.* from emp e) emp_temp
where emp_temp.row_num > 10; -- You can get ten records later

Explain it. : because rownum Is a pseudo column added to the result set , That is, a column added after finding the result set ( emphasize : You have to have a result set ). In short rownum Is the serial number of the result that meets the criteria , It always comes from 1 Start ranking , So you can't choose without 1, And there are more than 1 Value .

With the above knowledge , It's not hard to explain the following phenomenon :

① Why? select * from emp where rownum != 10 Always back before 9 Bar record ?

Because after the query to the result set , Show the end of 9 Bar record , The record after that is != 10.

② Why? rownum > 1 There's not a single record , and rownum > 0 or rownum >= 1 But it always shows all the records ?

because rownum It is added after the result set is found , It always comes from 1 Start .

③ Why? between 1 and 10 perhaps between 0 and 10 We can find out , and between 2 and 10 But no results ?

The reason as above , Less rownum=1 It's like a castle in the air .

[ application ]

A lot of nonsense , What's the use of these two guys ?

Hey , Don't worry. , Here's the main dish ...

problem : Get the top three salaries

analysis : The assumption is sql server Just fine , We can use topkeyword, oracle We use rownum Can also solve

 select *
from (select * from emp order by sal desc)
where rownum <= 3
order by rownum asc

Discuss oracle stay rowid and rownum More articles about

  1. Oracle Of RowId and Rownum

    This article refers to :https://www.cnblogs.com/whut-helin/p/8024860.html from sql select p.*,rowid,rownum from promotion ...

  2. oracle Of rowid and rownum

    rowid It's the only one that marks the physical location id, about rownum It is oracle The system is sequentially assigned the number of rows returned from the query , The first line returned allocates 1, The second line is 2, And so on , This pseudo field can be used to limit the total number of rows returned by the query ...

  3. 【 Reprint 】oracle And rowid Detailed explanation

    original text URL:http://www.2cto.com/database/201109/104961.html This article is about oracle from 8i Introduction object After the concept of rowid, That is to expand (exte ...

  4. Use rowid and rownum Problems to pay attention to when getting records

    We know .rowid and rownum stay Oracle All of them can be used as shoddy , It is mainly used to locate specific records in the table , But they are different ,rowid It corresponds to the physical address of the row record . and rownum It is not , Is through the returned recordset ...

  5. oracle_SQL in ROWID And ROWNUM Use ( turn )

    from :http://www.360doc.com/content/12/0802/11/219024_227802569.shtml rownum: ROWNUM Is a pseudo column added to the result set , That is to find out the results first ...

  6. Oracle-12: Pseudo column rowid and rownum

    ------------ I have no other , Only hand ripe , Humble as a fool , Eager to learn ------------- Pseudo column : The unreal is stored in the true table , But we can find that we can't add, delete or modify the pseudo column ! You can use rownum Come to share !!!!!! ...

  7. All kinds of Database Paging statements and Oracle In the database ROWNUM and ORDER BY The difference between

    .oracle Database Paging select * from (select a.*,rownum rc from Table name where rownum<=endrow) a where a.rc>=s ...

  8. oracle And Pseudo column rownum and rowid The usage and difference of

    rownum Usage of select  rownum,empno,ename,job from emp where rownum<6 We can get less than 6 Value data for select rownum,empno, ...

  9. oracle in row_number and rownum The difference and connection ( translate )

    http://www.tuicool.com/articles/bI3IBv Additional questions : There is one of the following SQL sentence : SELECT * FROM ( SELECT t.*, row_number() OVER ...

Random recommendation

  1. div The box is centered vertically and horizontally

    div The box , Horizontal vertical center . <!DOCTYPE html><html> <head> <meta charset="utf-8"> ...

  2. hash surface C Language implementation

    Algorithm reference < Introduction to algorithms > The first 11 Chapter 2 hash table . Use chain address method to solve conflicts . #include <stdio.h> #include <stdlib.h> #include < ...

  3. Makefile Classic tutorial ( Mastering these is enough )

    makefile Very important What is? makefile? Maybe a lot Winodws None of our programmers know about it , Because of those Windows Of IDE They've done this job for you , But I feel like making a good one and professional The programmer ...

  4. iOS Development ——UI piece &amp; Text gradient effect : In layers mask attribute

    Text gradient effect : In layers mask attribute This article , It's mainly about the mask attribute , Take advantage of it , You can make text gradients ! One . Text gradient effect : Two . The realization idea of text gradient : 1. Create a color gradient , Gradient layer and text control ...

  5. first App“ Today's material quotation ” shelves , Make a note of 【 primary 】

    App Store Address :https://itunes.apple.com/us/app/jin-ri-cai-liao-bao-jia/id967274552?l=zh&ls=1&mt ...

  6. [C#] To judge whether it is lawful IP4,IP6 Address

    Judge a string if there is no port , utilize IPAddress.TryParse Good judgment , How to judge if there is a port , Regular expressions ? Or some other way ? Key code : /// <summary> /// Determine whether ...

  7. Image processing Base64

    If the picture resources on the web page adopt http Formal url An additional request will be sent , The web page sent http More requests , The slower the page loads . And use Base64 Encoding of format , After converting the image to a string , The picture file will ...

  8. CASE WHEN High-order usage ?

    When two tables are associated , Subject to the left table , If a column in the left table is not empty , Then it is associated with the corresponding column of the right table , If it is empty, no matching will be made . The above practice , There's an unspeakable feeling , No matter what , The problem is solved . If there is a better solution , Please leave a message , Not very grateful !

  9. combox Three level linkage of provinces, cities and counties

    /** * Name Get provinces ( initialization ) */ function showProvince(id1, id2, id3) { var paramData = {}; $.ajax({ url: osp ...

  10. 《how tomcat works》 Reading notes - 2 - Facade design pattern , Avoid casts

    In chapter two 2.3 In the festival try { servlet = (Servlet) myClass.newInstance(); servlet.service((ServletRequest) request ...