如何在 MySQL 中制作行生成器?

2021-11-20 00:00:00 generator sql mysql

有没有办法生成可以在类似于 Oracle 语法的 JOIN 中使用的任意数量的行:

Is there a way to generate an arbitrary number of rows that can be used in a JOIN similar to the Oracle syntax:

SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=10

推荐答案

讨厌这么说，但是 MySQL 是四大中唯一一个没有的 RDBMS有这个功能.

Hate to say this, but MySQL is the only RDBMS of the big four that doesn't have this feature.

在Oracle中:

SELECT  *
FROM    dual
CONNECT BY
        level < n

在MS SQL中(最多100行):

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < n
        )
SELECT  *
FROM    hier

或使用提示32768

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < 32768
        )
SELECT  *
FROM    hier
OPTION (MAXRECURSION 32767) -- 32767 is the maximum value of the hint

在PostgreSQL中:

SELECT  *
FROM    generate_series (1, n)

在MySQL中，什么都没有.

相关文章