我需要与一个包含整数n
到m
(包括n和m)的表/结果集进行连接。是否有一种简单的方法可以避免手动构建该表?
(顺便问一下,这种类型的结构被称为"元查询"吗?)
m-n
的值被限制在合理范围内(小于1000)。
我需要与一个包含整数n
到m
(包括n和m)的表/结果集进行连接。是否有一种简单的方法可以避免手动构建该表?
(顺便问一下,这种类型的结构被称为"元查询"吗?)
m-n
的值被限制在合理范围内(小于1000)。
您似乎可以使用以下方法构建较大的集合:
select 9 colname union all select 10 union all select 11 union all select 12 union all select 13 ...
我在5.0.51a版本中遇到了5300的解析器堆栈溢出错误。
data:text/html,<script>for(var x=1;x<=5678;++x)document.write((x===1?'':' union all ')+'select '+x);</script>
。 - Pacerierselect ((((((b7.0 << 1 | b6.0) << 1 | b5.0) << 1 | b4.0)
<< 1 | b3.0) << 1 | b2.0) << 1 | b1.0) << 1 | b0.0 as n
from (select 0 union all select 1) as b0,
(select 0 union all select 1) as b1,
(select 0 union all select 1) as b2,
(select 0 union all select 1) as b3,
(select 0 union all select 1) as b4,
(select 0 union all select 1) as b5,
(select 0 union all select 1) as b6,
(select 0 union all select 1) as b7
没有独特或排序阶段,没有字符串到数字的转换,没有算术运算,每个虚拟表只有2行,因此速度应该非常快。
这个版本使用8个“位”,所以它从0计数到255,但你可以轻松调整它。
这个查询生成从0到1023的数字。我相信它在任何SQL数据库中都可以使用:
select
i0.i
+i1.i*2
+i2.i*4
+i3.i*8
+i4.i*16
+i5.i*32
+i6.i*64
+i7.i*128
+i8.i*256
+i9.i*512
as i
from
(select 0 as i union select 1) as i0
cross join (select 0 as i union select 1) as i1
cross join (select 0 as i union select 1) as i2
cross join (select 0 as i union select 1) as i3
cross join (select 0 as i union select 1) as i4
cross join (select 0 as i union select 1) as i5
cross join (select 0 as i union select 1) as i6
cross join (select 0 as i union select 1) as i7
cross join (select 0 as i union select 1) as i8
cross join (select 0 as i union select 1) as i9
从Mariadb 10.2开始(MySQL现在也有了),您可以使用公共表达式(CTE)非常优雅地完成此操作。
下面是具体步骤:
with recursive numbers (n) as (
select 1 as n
union
select n+1 from numbers where n<100
)
select * from numbers
numbers
公用表与您自己的表连接起来,从而实现1..n(或使用同样的方法实现n..m)。m的大小是多少?
你可以做这样的事情:
create table two select null foo union all select null;
create temporary table seq ( foo int primary key auto_increment ) auto_increment=9 select a.foo from two a, two b, two c, two d;
select * from seq where foo <= 23;
在自增字段设置为n,where子句与m进行比较,并且重复使用两个表的次数至少为ceil(log(m-n+1)/log(2))。
(通过将two替换为(select null foo union all select null)来创建临时表seq可省略非临时两个表。)
with t1 as (
select 0 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
)
SELECT ROW_NUMBER() over ()
FROM
t1,
t1 as t2;
请尝试以下内容
select
ROW_NUMBER() OVER (ORDER BY id asc) AS 'rowId', otherField from table;
警告:如果您逐行插入数字,最终将执行N个命令,其中N是需要插入的行数。
通过使用临时表(请参见下面插入10000到10699之间的数字),您可以将此操作降至O(log N)。
mysql> CREATE TABLE `tmp_keys` (`k` INTEGER UNSIGNED, PRIMARY KEY (`k`));
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO `tmp_keys` VALUES (0),(1),(2),(3),(4),(5),(6),(7);
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+8 from `tmp_keys`;
Query OK, 8 rows affected (0.02 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+16 from `tmp_keys`;
Query OK, 16 rows affected (0.03 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+32 from `tmp_keys`;
Query OK, 32 rows affected (0.03 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+64 from `tmp_keys`;
Query OK, 64 rows affected (0.03 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+128 from `tmp_keys`;
Query OK, 128 rows affected (0.05 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+256 from `tmp_keys`;
Query OK, 256 rows affected (0.03 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `tmp_keys` SELECT k+512 from `tmp_keys`;
Query OK, 512 rows affected (0.11 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> INSERT INTO inttable SELECT k+10000 FROM `tmp_keys` WHERE k<700;
Query OK, 700 rows affected (0.16 sec)
Records: 700 Duplicates: 0 Warnings: 0
编辑:很遗憾,这不能在真正的MySQL 5.0临时表中使用,因为它无法插入自身(您可以在两个临时表之间来回跳转)。
编辑:您可以使用MEMORY存储引擎来防止这实际上对“真实”数据库造成负担。我想知道是否有人开发了一个“NUMBERS”虚拟存储引擎来实例化虚拟存储以创建这样的序列。(遗憾的是,在MySQL之外不可移植)
如果您使用的是Oracle,那么“管道函数”将是最佳选择。不幸的是,MySQL没有这样的结构。
根据您需要的数字规模,我看到有两种简单的方法:您可以为单个查询使用存储过程填充仅包含所需数字的临时表(可能使用内存表),或者您可以预先构建一个从1计数到1,000,000的大表,并选择其有界区域。