使用遞歸CTE可跨多數(shù)據(jù)庫生成數(shù)字序列，如PostgreSQL和SQL Server中從1到10的序列；2. PostgreSQL和Oracle支持SEQUENCE對象，適合生成自增ID，且PostgreSQL可用generate_series函數(shù)快速生成范圍；3. MySQL 8.0 支持遞歸CTE模擬序列，而早期版本需通過交叉聯(lián)接構(gòu)造輔助表生成；4. SQL Server 2022 提供GENERATE_SERIES函數(shù)直接生成序列；5. 對于不支持遞歸的系統(tǒng)，可通過構(gòu)建臨時表與笛卡爾積生成小規(guī)模序列。

To generate a sequence of numbers in SQL, the method you use depends on your database system. Most databases offer built-in functions or techniques to create number sequences efficiently. Below are common approaches across different SQL platforms.

Using Recursive CTE (Common Table Expression)

This method works in many modern databases like PostgreSQL, SQL Server, and SQLite. It starts with an initial value and recursively increments until a limit is reached.

Example: Generate numbers from 1 to 10

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n   1 FROM numbers WHERE n 

Using Built-in Sequence Objects (PostgreSQL, Oracle)

Databases like PostgreSQL and Oracle support SEQUENCE, which is ideal for generating ordered numeric values, often used for IDs.

Create and use a sequence:

CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1;
<p>-- Get next value
SELECT NEXTVAL('my_seq');</p><p>-- Generate multiple numbers using generate_series (PostgreSQL)
SELECT * FROM generate_series(1, 10);</p>

Using System-Generated Number Functions

Some databases provide functions to generate number series directly.

• MySQL: No direct function, but you can simulate with recursive CTE (MySQL 8.0 )
• SQL Server: Use GENERATE_SERIES() (available in SQL Server 2022 )

  SELECT value FROM GENERATE_SERIES(1, 10);
  

• SQLite: Supports recursive CTE similar to PostgreSQL

Simulating Sequences with Temporary Data

If recursive queries aren't available, you can use a helper table or cross-joined values to generate small sequences.

Example in MySQL (pre-8.0):

SELECT 
  thousands.n * 1000   hundreds.n * 100   tens.n * 10   units.n   1 AS num
FROM 
  (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION ... up to 9) units,
  (SELECT 0 AS n UNION SELECT 1 UNION ... 9) tens,
  (SELECT 0 AS n UNION ... 9) hundreds,
  (SELECT 0 AS n UNION ... 9) thousands
ORDER BY num;

This generates a large range using Cartesian product — useful for legacy systems.

Basically, pick the method that matches your database version and needs. Recursive CTEs are widely supported and flexible. For PostgreSQL and newer SQL Server, built-in generators make it simple. Just ensure performance is acceptable for large ranges.

以上是如何在 SQL 中生成數(shù)字序列的詳細(xì)內(nèi)容。更多信息請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！