如何选择1亿x的单行 [英] How to select a single row a 100 million x

问题描述

我想使用select中的一行来生成以下输出.

I would like to generate the following output, using a single row from a select.

SELECT max(t1.id)+1 as new_id FROM t1;
-> 101

However I want to do

SELECT s.last_id, sequence(1..100000000) as new_id 
FROM (SELECT max(table1.id)+1 as last_id FROM table1) s;
-> 101,1
-> 101,2
-> 101,3 
......
-> 101,100000000

在postgreSQL中，我可以使用:

In postgreSQL I can do this using:

SELECT s.last_id, generate_series(1,100000000) 
FROM (SELECT max(table1.id)+1 as last_id FROM table1) s;  -- returns 100,000,000 rows 

如何在不使用临时表的情况下在 MySQL 中执行此操作?

How do I do this in MySQL without using a temp-table?

推荐答案

对布鲁诺的解决方案稍作修改

Slight amend to Bruno's solution

SELECT (SELECT COALESCE(max(id),0)+1 FROM table1), 
        @rownum:=@rownum+1 new_id 
FROM 
(SELECT @rownum:=0) r, 
(SELECT 1 UNION ALL SELECT 2) t1,
(SELECT 1 UNION ALL SELECT 2) t2,
(SELECT 1 UNION ALL SELECT 2) t3,
(SELECT 1 UNION ALL SELECT 2) t4,
(SELECT 1 UNION ALL SELECT 2) t5,
(SELECT 1 UNION ALL SELECT 2) t6,
(SELECT 1 UNION ALL SELECT 2) t7
LIMIT 100

或其他没有变量的版本

SELECT (SELECT Coalesce(MAX(id), 0) + 1
        FROM   table1),
       t1.n * 10 + t2.n + 1 AS new_id
FROM   (SELECT 0 AS n UNION ALL
        SELECT 1      UNION ALL
        SELECT 2      UNION ALL
        SELECT 3      UNION ALL
        SELECT 4      UNION ALL
        SELECT 5      UNION ALL
        SELECT 6      UNION ALL
        SELECT 7      UNION ALL
        SELECT 8      UNION ALL
        SELECT 9) t1,
       (SELECT 0 AS n UNION ALL
        SELECT 1      UNION ALL
        SELECT 2      UNION ALL
        SELECT 3      UNION ALL
        SELECT 4      UNION ALL
        SELECT 5      UNION ALL
        SELECT 6      UNION ALL
        SELECT 7      UNION ALL
        SELECT 8      UNION ALL
        SELECT 9) t2
ORDER  BY new_id  

这篇关于如何选择1亿x的单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！