SQL SQLite 循环语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7370761/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite loop statements?
提问by kseen
Is there any loop statements in SQLite like FOR .. in .. LOOPor something like that? I have two columns StartRange, EndRangeand I need to insert whole sequence in other table. So if StartRangeis 1 and EndRangeis 3 it's necessary to make three inserts with value, contains 1, 2, 3.
SQLite 中是否有任何循环语句之FOR .. in .. LOOP类的？我有两列StartRange, EndRange，我需要在其他表中插入整个序列。因此，如果StartRange是 1 并且EndRange是 3，则需要使用值进行三个插入，包含1, 2, 3。
采纳答案by mu is too short
You can do this sort of thing in straight SQL if you have an extra table that holds all the integers that you need.
如果你有一个额外的表来保存你需要的所有整数，你可以在直接 SQL 中做这种事情。
Suppose your StartRangeand EndRangerange between one and ten and you have a table like this:
假设您的StartRangeandEndRange范围在 1 到 10 之间，并且您有一张这样的表：
sqlite> select i from ints;
i
1
.
.
.
10
This table simply contains all the possible integers that you need (i.e. one through ten).
该表仅包含您需要的所有可能的整数（即一到十）。
Then if you also have this:
那么如果你也有这个：
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);
You can do your INSERTs into targetwith a join:
您可以通过连接执行 INSERT 操作target：
insert into target (i)
select ints.i
from ints join t on (ints.i >= t.startrange and ints.i <= t.endrange)
The result is this:
结果是这样的：
sqlite> select * from target;
i
1
2
3
Of course your real twould have more rows so you'd want a WHERE clause to limit which row of tyou look at.
当然，你的真实t会有更多的行，所以你需要一个 WHERE 子句来限制t你看哪一行。
Similar things are often done with dates (look up "calendar tables").
类似的事情通常用日期来完成（查找“日历表”）。
So if your ranges are small (for some definition of small) then generate your intstable 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 intstable but SQLite is (intentionally) limited.
所以，如果你的范围小（一些定义小），那么你的生成ints表一次，添加一个索引到它，并使用上述技术做对数据库内的所有插件。其他数据库有自己的方法（例如 PostgreSQL 的generate_series）来做这种事情而不需要一个明确的ints表，但 SQLite 是（有意）限制的。
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.
SQL 通常是基于集合的，因此循环不自然。通过描述您的需要来构建适当的集合是很自然的。OTOH，有时不自然的行为是必要且明智的。
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.
我不知道这对您的应用程序是否有意义，我只是想演示一下如何完成。如果这种方法在您的情况下没有意义，那么您可以在数据库外生成一堆 INSERT 语句。
回答by Doug Currie
You can make loops in SQL with recursive triggers. Using mu is too short's schema
您可以使用递归触发器在 SQL 中创建循环。使用mu的架构太短
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);
we need to enable recursive triggers in SQLite:
我们需要在 SQLite 中启用递归触发器：
sqlite> PRAGMA recursive_triggers = on;
Make a temporary trigger to loop up to the end of the range:
制作一个临时触发器以循环到范围的末尾：
sqlite> create temp trigger ttrig
...> before insert on target
...> when new.i < (select t.endrange from t) begin
...> insert into target values (new.i + 1);
...> end;
Kick it off:
开始吧：
sqlite> insert into target values ((select t.startrange from t));
sqlite> select * from target;
3
2
1
sqlite>
回答by rob
Apparently the looping construct in SQLite is the WITH RECURSIVEclause. That documentation link has sample count-to-ten code, a Mandelbrot set plotter, and a Sudoku puzzle solver, all in pure SQL. Here's an SQLite query that computes the Fibonacci sequence to give you a feel for it:
显然，SQLite 中的循环结构是WITH RECURSIVE子句。该文档链接包含示例计数到十代码、Mandelbrot 集绘图仪和数独谜题求解器，所有这些都使用纯 SQL。这是一个计算斐波那契数列的 SQLite 查询，让您感受一下：
sqlite> WITH RECURSIVE
...> fibo (curr, next)
...> AS
...> ( SELECT 1,1
...> UNION ALL
...> SELECT next, curr+next FROM fibo
...> LIMIT 100 )
...> SELECT group_concat(curr) FROM fibo;
1,1,2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765,10946,...
And here's a Sieve of Eratosthenes:
begin transaction;
drop table if exists naturals;
create table naturals
( n integer unique primary key asc,
isprime bool,
factor integer);
with recursive
nn (n)
as (
select 2
union all
select n+1 as newn from nn
where newn < 1e4
)
insert into naturals
select n, 1, null from nn;
insert or replace into naturals
with recursive
product (prime,composite)
as (
select n, n*n as sqr
from naturals
where sqr <= (select max(n) from naturals)
union all
select prime, composite+prime as prod
from
product
where
prod <= (select max(n) from naturals)
)
select n, 0, prime
from product join naturals
on (product.composite = naturals.n)
;
commit;
