1、 生成連續整數序列
MySQL8: with recursive t(n) as (
select 1
union all
select n+1 from t where n<7
)
select * from t;
Oracle：select level n
from dual connect by level<=7;
集算器 SPL：
A1：構造從 1 到 7 的整數序列
示例 1：百雞問題，雞翁一值錢五，雞母一值錢三，雞雛三值錢一。百錢買百雞，問雞翁、母、雛各幾
MySQL8:
with recursive jg(n) as (select 1 union all select n+1 from jg where n<100/5),
jm(n) as (select 1 union all select n+1 from jm where n<100/3),
jc(n) as (select 3 union all select n+3 from jc where n<98)
select jg.n jw, jm.n jm, jc.n jc
from jg cross join jm cross join jc
where jg.n*5+jm.n*3+jc.n/3=100 and jg.n+jm.n+jc.n=100
集算器 SPL：
A1：構造1到20的整數序列
A2：構造1到33的整數序列
A3：構造1到99且步長為3的整數序列
A4：建立資料結構為(jw,jm,jc)的序表
A5：對A1、A2、A3的資料進行巢狀迴圈，若滿足於A1成員+A2成員+A3成員==100且A1成員*5+A2成員*3+A3成員/3==100則追加到A4序表中
示例2：將指定列中冒號分隔的串劃分成多行
Oracle:
with t(k,f) as (select 1 , `a1:a2:a3` from dual
union all select 2, `b1:b2` from dual),
t1 as (select k,f, length(f)-length(replace(f,`:`,“))+1 cnt from t),
t2 as (select level n from dual connect by level<=(select max(cnt) from t1)),
t3 as (select t1.k, t1.f, n, cnt,
case when n=1 then 1 else instr(f,`:`,1,n-1)+1 end p1,
case when n=cnt then length(f)+1 else instr(f,`:`,1,n) end p2
from t1 join t2 on t2.n<=t1.cnt)
select k,substr(f,p1,p2-p1) f from t3 order by k;
集算器 SPL：
A1：建立資料結構為(k,f)的序表，並追加2條記錄(1, “a1:a2:a3)和(2,”b1:b2”)
A2：將A1的欄位f用冒號劃分成序列並重新賦值給欄位f
A3：針對A1每條記錄構造資料結構為(k,f)的序表，並根據欄位f中成員構造記錄(A1.k，f成員)追加到此序表中
2、 生成連續日期序列
MySQL8：
with recursive
t(d) as (select date`2018-10-03`
union all
select d+1 from t where d<date`2018-10-09`)
select d,dayofweek(d) w from t;
集算器 SPL：
A1：生成2018-10-03到2018-10-09的日期序列
示例：列出2015-01-03到2015-01-07每天的銷量彙總
MySQL8：
with recursive
t(d,v) as (select date`2015-01-04`,30
union all select date`2015-01-06`,50
union all select date`2015-01-07`,50
union all select date`2015-01-03`,40
union all select date`2015-01-04`, 80),
s(d) as (select date`2015-01-03`
union all
select d+1 from s where d<date`2015-01-07`)
select s.d, sum(t.v) v
from s left join t on s.d=t.d
group by s.d;
集算器 SPL：
A4：A2中記錄按欄位d的值對齊到A3
A5：根據A4和A3對位構造統計後的序表
3、 生成連續的工作日(不包含週六週日)序列
MySQL8:
with recursive
t(d) as (select date`2018-10-03`
union all
select d+1 from t where d<date`2018-10-09`)
select d,dayofweek(d) w from t
where dayofweek(d)<=5;
集算器 SPL：
A1：構造從2018-10-03到2018-10-09不包含週六週日的日期序列
A2：根據A1構造日期及相應周幾的序表
4、 根據序列生成表
MySQL8：
with recursive t1(n) as (select 1 union all select n+1 from t1 where n<14),
t2(n, name) as (select n, concat(`a`,n) name from t1)
select max(if(n%4=1, name, null)) f1,
max(if(n%4=2, name, null)) f2,
max(if(n%4=3, name, null)) f3,
max(if(n%4=0, name, null)) f4
from t2
group by floor((n+3)/4);
集算器 SPL：
