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

Today, I will talk to you about what the limiting conditions of DMSQL LIMIT are. Many people may not know much about it. In order to let you know more, Xiaobian summarizes the following contents for you. I hope you can gain something according to this article.

In DM, you can filter the result set using qualifying conditions, supporting LIMIT clause and ROW_LIMIT clause.

LIMIT clause

LIMIT clause selects N records starting with a record in the result set in order. syntax is as follows

::= LIMIT

>

|

>

>

::=

::=

Three ways are supported:

1. LIMIT N: Select the first N records;

2. LIMIT M,N: Select N records after the Mth record;

3. LIMIT M OFFSET N: Select M records after record N.

Note: LIMIT cannot appear with TOP in a query statement.

For example, query the first 2 records

SQL> SELECT PRODUCTID , NAME FROM PRODUCTION.PRODUCT LIMIT 2;LINEID PRODUCTID NAME---------- ----------- ------1 1 Dream of Red Mansions 2 2 used time: 1.236(ms). Execute id is 39690.

For example, look up the serial numbers and names of the third and fourth registered products.

SQL> SELECT PRODUCTID, NAME FROM PRODUCTION.PRODUCT LIMIT 2 OFFSET 2;LINEID PRODUCTID NAME---------- ----------- --------------------1 3 Old Man and Sea 2 4 used time: 1.035(ms). Execute id is 39698.

For example, look up the first 5, 6, 7 registered names.

SQL> SELECT PERSONID,NAME FROM PERSON.PERSON LIMIT 4,3;LINEID PERSONID NAME---------- ----------- ----1 5 Sun Li 2 6 Huang Fei 3 7 Faye Wong used time: 1.137(ms). Execute id is 39701.

ROW_LIMIT clause

Used to specify the number of rows or percentage of rows offset from the query result, so as to obtain the query result more flexibly.

syntax is as follows

::= [OFFSET ] []

::= FETCH

[PERCENT]

Parameter Description:

offset: Specifies the starting offset of the rows returned by the query.

FIRST |NEXT: FIRST starts at offset 0. NEXT, to get results starting at the next line at the specified offset. It is only a function of explanation, not an actual limitation.

[PERCENT]: Specifies the number of rows (without PERCENT) or percentage (with PERCENT) returned. ONLY |WITH TIES: Specifies whether the result set returns additional rows. Extra rows are all rows sorted by the same sort key as the last row. ONLY returns only the specified number of rows. WITH TIES must appear with an ORDER BY clause, ignoring WITH TIES if there is no ORDER BY clause.

Parameter limits:

offset: must be a number. If offset is negative, it is considered 0. offset is NULL or greater than or equal to the number of rows returned, returning 0 rows. When offset is a decimal, the decimal is truncated.

2、

: Only numbers. When percent is specified as a negative number, it is treated as 0%. percent is NULL and returns 0 rows. If percent is not specified, return 1 line.

3, can not be used together.

4. Option is used, select column cannot contain CURRVAL or NEXTVAL pseudo-column.

5. The query definition of the view contains, this view will not be incrementally refreshed.

For example, look up the cheapest 50% of items

SQL> SELECT NAME, NOWPRICE FROM PRODUCTION.PRODUCT ORDER BY NOWPRICE FETCH FIRST 50 PERCENT ROWS ONLY;LINEID NAME NOWPRICE---------- ---------------- --------1 Old Man and the Sea 6.10002 Breaking through basic English vocabulary 11.10003 There are no small things at work. 11.40004 water Margin 14.30005 dream of red 15.2000used time: 2.689(ms). Execute id is 39731.

For example, query the price of the third cheapest start of the three records

SQL> SELECT NAME, NOWPRICE FROM PRODUCTION.PRODUCT ORDER BY NOWPRICE OFFSET 2 ROWS FETCH FIRST 3 ROWS ONLY;LINEID NAME NOWPRICE---------- ------------ --------1 Nothing small at work 11.40002 water Margin 14.30003 dream of red 15.2000used time: 2.543(ms). Execute id is 39735. After reading the above content, do you have any further understanding of what is the qualification condition of DMSQL LIMIT? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

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.