SQL难点解决：序列生成

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：

	A
1	=to(1,7)

A1：构造从 1 到 7 的整数序列

SQL 难点解决：序列生成

示例 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：

	A
1	=to(100/5)
2	=to(100/3)
3	=33.(~*3)
4	=create(jw,jm,jc)
5	>A1.run(A2.run(A3.run(if(A1.~+A2.~+A3.~==100 && A1.~5+A2.~3+A3.~/3==100,A4.insert(0,A1.~,A2.~,A3.~)))))

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序表中

SQL 难点解决：序列生成

示例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：

	A
1	=create(k,f).record([1,"a1:a2:a3",2,"b1:b2"])
2	>A1.run(f=f.split(":"))
3	=A1.(f.new(A1.k:k, ~:f))
4	=A3.conj()

A1：创建数据结构为(k,f)的序表，并追加2条记录(1, “a1:a2:a3)和(2,”b1:b2”)

A2：将A1的字段f用冒号划分成序列并重新赋值给字段f

A3：针对A1每条记录构造数据结构为(k,f)的序表，并根据字段f中成员构造记录(A1.k，f成员)追加到此序表中

SQL 难点解决：序列生成

2、生成连续日期序列

MySQL8：

with recursive

t(d) as (select date'2018-10-03'

union all

select d+1 from t where d

select d,dayofweek(d) w from t;

集算器 SPL：

	A
1	=periods("2018-10-03", "2018-10-09")

A1：生成2018-10-03到2018-10-09的日期序列

SQL 难点解决：序列生成

示例：列出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

select s.d, sum(t.v) v

from s left join t on s.d=t.d

group by s.d;

集算器 SPL：

	A
1	[2015-01-04, 30, 2015-01-06,50, 2015-01-07,50, 2015-01-03,40, 2015-01-04,80]
2	=create(d,v).record(A1)
3	=periods("2015-01-03", "2015-01-07")
4	=A2.align@a(A3,d)
5	=A4.new(A3(#):d, ~.sum(v):v)