如何在MySQL中创建行生成器? [英] How do I make a row generator in MySQL?

问题描述

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

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中什么都没有.

这篇关于如何在MySQL中创建行生成器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！