SQL query displays line number, random query, fetch specified row data

1, display line number

If the data is not deleted, the primary key is consistent with the row number, but when some data is deleted, the row number is inconsistent with the primary key, and the need to query the line number requires a new method, and the temporary table is required before SQL Server2005, but in SQL Server2005, using the Row_ Number () is very convenient.

selectrow_number() over (order by UseriD) as rowNum,* fromUserInfo

Query Result:

2. Random Query

Sometimes the data we need to query is randomly sorted, and the NEWID () function generates a random value when each record is scanned, the random number is not in the case order, so the data can be randomly sorted by this number.

select*,newid() random from UserInfo order byrandom

Query Result:

3, take the specified row of data

What we need here is not the data of the primary key number within a range of values, but the line number within the specified range, or the row_number () function above.

select* from (select row_number() over (order by UseriD) as rowNum,* from UserInfo) as t where rowNum between 10 and20

This allows us to take 10 to 20 rows of data that are 11 rows in total.

SQL query displays line number, random query, fetch specified row data