How to Generate a Number List from 1 to 100 Using SQL's DUAL Table?

Generating a Number List from 1 to 100 with SQL

It is possible to utilize the DUAL table in SQL to ascertain a series of whole numbers ranging from 1 to 100.

Problem:

How can I generate a list of numbers from 1 to 100 using the DUAL table?

Answer:

To effectively select the desired sequence of numbers, execute the following SQL statement:

Select Rownum r
From dual
Connect By Rownum <= 100

This query employs the DUAL table, a built-in virtual table that consistently yields a solitary row with the value NULL. The ROWNUM pseudocolumn assigns an ascending sequential number to each row in the result set, starting from 1 for the initial row.

The CONNECT BY clause is utilized to recursively generate a connected hierarchy of rows, with the condition ROWNUM <= 100 limiting the hierarchy to 100 rows. Each row in the hierarchy has the same ROWNUM value as its parent row, except for the initial row, which has a ROWNUM value of 1.

By selecting the ROWNUM values from the resulting hierarchy, we obtain a list of numbers ranging sequentially from 1 to 100. This method effectively achieves the desired outcome without relying on user-defined functions or complex JOIN operations.

The above is the detailed content of How to Generate a Number List from 1 to 100 Using SQL's DUAL Table?. For more information, please follow other related articles on the PHP Chinese website!