DB2 SQL query result set automatic numbering, return bar number range control Research
Summary:the DB2 SQL statement implements the record of the query automatically numbering, and further realizes the record range control according to the number, the paging query.
Environment:IBM DB2 Universal V9.1Windows XP ProfessionalRequirements: Write an SQL statement, in the query results to display the record sequence number, this sequence number is automatically generated according to the order of record.
Test Initialize the DB2 SQL script: drop table t_test;CREATE TABLE T_test(BS bigint NOT NULL is NULL generated by default as identity,username varchar () not NULL,password varchar (+),remark varchar ($) );comment on T_test(BS is ' logo ',username is ' user name ',password is ' password ',remark is ' remarks ' );INSERT into t_test (username, password) VALUES (' AAA ', ' AAA '),(' BBB ', ' BBB '),(' CCC ', ' CCC '),(' ddd ', ' ddd '),(' eee ', ' eee '),(' FFF ', ' FFF '),(' GGG ', ' GGG ');
implementation:1, the implementation of the query record numberRequirements: Query BS is not equal to 1 of all records. Select T.*, Row_number () over () as Row_noFrom t_test Twhere t.bs <> 1The query results are as follows:BS username password remark Row_no---------------------------------------2 ' BBB ' BBB ' [Null] ' 13 ' CCC ' CCC ' [Null] ' 24 ' ddd ' DDD ' [Null] ' 35 ' eee ' eee ' [Null] ' 46 ' FFF ' FFF ' [Null] ' 57 ' ggg ' GGG ' [Null] ' 6 Description: Row_number () over () is used to generate a numbered sequence, numbering starting from 1, as an integer. As Row_no sets the display name for the numbered column. 2, the realization according to the record scope inquiryRequirements: Query 第2-5条 records are required. SELECT *From (select T.*, Row_number () over () as Row_noFrom T_test t) as Wwhere Row_no >= 2and row_no <=5The query results are as follows:BS username password remark Row_no---------------------------------------2 ' BBB ' BBB ' [Null] ' 23 ' CCC ' CCC ' [Null] ' 34 ' ddd ' DDD ' [Null] ' 45 ' eee ' eee ' [Null] ' 53. Implementing SQL Paging Queryto extend the SQL in 2, you can implement a paged query by passing the range start number as a variable to SQL. Of course, this also requires the support of the program, master this principle is very easy, do not introduce.
Summary:The control of record return is the characteristic of database system, and different database systems have different implementations. SQL is generally best for controlling the efficiency of result set records directly. Combined with the advanced programming language, complex paging queries can be implemented. ---------finished---------
