|
|
In this example we will create tally table of 100 sequential numbers using
two table of 10 records each and only one query. First create tables and insert
initial data:
CREATE TABLE num_tb
(num Integer,
CONSTRAINT pk_num PRIMARY KEY (num));
CREATE TABLE k_tb
(k Integer,
CONSTRAINT pk_k PRIMARY KEY (k));
|
num_tb will store values:
| num |
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
k_tb will store values:
| k |
| 2.18 |
| 3.33 |
| 3.69 |
| 3.89 |
| 4.17 |
| 4.52 |
| 4.58 |
| 4.63 |
| 4.71 |
| 4.83 |
Following query creates tally table of 100 sequential numbers from 1 to 100:
SELECT t1.n1 INTO num_seq
FROM
(
SELECT c.no AS n1
FROM
(
SELECT (a.num + b.num) AS no
FROM num_tb AS a, num_tb AS b
UNION ALL
SELECT (a.num * b.num) AS no
FROM num_tb AS a, num_tb AS b
UNION ALL
SELECT (a.num + b.num) AS no
FROM num_tb AS a, num_tb AS b
UNION ALL
SELECT (a.num * b.num) AS no
FROM num_tb AS a, num_tb AS b
) AS c
GROUP BY c.no
UNION ALL
SELECT (c.no * d.num) AS n1
FROM
(
SELECT (a.num + b.num) AS no
FROM num_tb AS a, num_tb AS b
) AS c, num_tb AS d
UNION ALL
SELECT Round(c.no * d.k) AS n1
FROM
(
SELECT (a.num + b.num) AS no
FROM num_tb AS a, num_tb AS b
) AS c, k_tb AS d
) AS t1
WHERE (((t1.n1) BETWEEN 1 AND 100))
GROUP BY t1.n1
ORDER BY t1.n1;
|
|