SQLite 循环语句? [英] SQLite loop statements?

问题描述

SQLite 中是否有任何循环语句，例如 FOR .. in .. LOOP 或类似的东西?我有两列 StartRange, EndRange 我需要在其他表中插入整个序列.因此，如果 StartRange 为 1 且 EndRange 为 3，则需要使用值进行三个插入，包含 1,2,3.

Is there any loop statements in SQLite like FOR .. in .. LOOP or something like that? I have two columns StartRange, EndRange and I need to insert whole sequence in other table. So if StartRange is 1 and EndRange is 3 it's necessary to make three inserts with value, contains 1, 2, 3.

推荐答案

如果你有一个额外的表来保存你需要的所有整数，你可以用直接的 SQL 来做这种事情.

You can do this sort of thing in straight SQL if you have an extra table that holds all the integers that you need.

假设您的 StartRange 和 EndRange 范围在 1 到 10 之间，并且您有一个像这样的表格:

Suppose your StartRange and EndRange range between one and ten and you have a table like this:

sqlite> select i from ints;
i
1
.
.
.
10

此表仅包含您需要的所有可能的整数(即一到十).

This table simply contains all the possible integers that you need (i.e. one through ten).

如果你也有这个:

sqlite> create table t (startrange int not null, endrange int not null);
sqlite> insert into t values(1, 3);
sqlite> create table target (i int not null);

您可以通过连接在 target 中插入:

You can do your INSERTs into target with a join:

insert into target (i)
select ints.i
from ints join t on (ints.i >= t.startrange and ints.i <= t.endrange)

结果如下:

sqlite> select * from target;
i
1
2
3

当然，您真正的 t 会有更多行，因此您需要一个 WHERE 子句来限制您查看的 t 的哪一行.

Of course your real t would have more rows so you'd want a WHERE clause to limit which row of t you look at.

类似的事情经常用日期来完成(查找日历表").

Similar things are often done with dates (look up "calendar tables").

因此，如果您的范围很小(对于 small 的某些定义)，则生成一次 ints 表，为其添加索引，然后使用上述技术来执行数据库中的所有 INSERT.其他数据库有自己的方式(比如PostgreSQL的generate_series) 无需显式 ints 表即可执行此类操作，但 SQLite(有意)受到限制.

So if your ranges are small (for some definition of small) then generate your ints table once, add an index to it, and use the above technique to do all the INSERTs right inside the database. Other databases have their own ways (such as PostgreSQL's generate_series) to do this sort of thing without need an explicit ints table but SQLite is (intentionally) limited.

SQL 通常是基于集合的，因此循环不自然.通过描述您的需要来构建适当的集合是很自然的.OTOH，有时不自然的行为是必要且明智的.

SQL is generally set-based so loops aren't natural. What is natural is building the appropriate sets by describing what you need. OTOH, sometimes unnatural acts are necessary and sensible.

我不知道这对您的应用程序是否有意义，我只是想我会演示如何做到这一点.如果这种方法在您的情况下没有意义，那么您可以在数据库外生成一堆 INSERT 语句.

I don't know if this makes sense for your application, I just thought I'd demonstrate how it can be done. If this approach doesn't make sense in your case then you can generate a bunch of INSERT statements outside the database.

这篇关于SQLite 循环语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！