这里有一种方法可以实现基于集的无循环。这也可以成为一个可重用的视图。这个例子显示了从0到999之间的序列的生成,但是当然,它可能会被修改以适应。INSERT INTO
myTable (
nr )SELECT
SEQ.SeqValueFROM(SELECT
(HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValueFROM
(
SELECT 0 SeqValue UNION ALL
SELECT 1 SeqValue UNION ALL
SELECT 2 SeqValue UNION ALL
SELECT 3 SeqValue UNION ALL
SELECT 4 SeqValue UNION ALL
SELECT 5 SeqValue UNION ALL
SELECT 6 SeqValue UNION ALL
SELECT 7 SeqValue UNION ALL
SELECT 8 SeqValue UNION ALL
SELECT 9 SeqValue ) ONESCROSS JOIN
(
SELECT 0 SeqValue UNION ALL
SELECT 10 SeqValue UNION ALL
SELECT 20 SeqValue UNION ALL
SELECT 30 SeqValue UNION ALL
SELECT 40 SeqValue UNION ALL
SELECT 50 SeqValue UNION ALL
SELECT 60 SeqValue UNION ALL
SELECT 70 SeqValue UNION ALL
SELECT 80 SeqValue UNION ALL
SELECT 90 SeqValue ) TENSCROSS JOIN
(
SELECT 0 SeqValue UNION ALL
SELECT 100 SeqValue UNION ALL
SELECT 200 SeqValue UNION ALL
SELECT 300 SeqValue UNION ALL
SELECT 400 SeqValue UNION ALL
SELECT 500 SeqValue UNION ALL
SELECT 600 SeqValue UNION ALL
SELECT 700 SeqValue UNION ALL
SELECT 800 SeqValue UNION ALL
SELECT 900 SeqValue ) HUNDREDS) SEQ