1、    生成連續(xù)整數(shù)序列

在益陽等地區(qū)，都構建了全面的區(qū)域性戰(zhàn)略布局，加強發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務理念，為客戶提供網(wǎng)站制作、做網(wǎng)站 網(wǎng)站設計制作按需策劃,公司網(wǎng)站建設,企業(yè)網(wǎng)站建設,品牌網(wǎng)站建設,成都全網(wǎng)營銷推廣,外貿(mào)網(wǎng)站建設,益陽網(wǎng)站建設費用合理。

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 的整數(shù)序列

 

示例 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的整數(shù)序列

A2：構造1到33的整數(shù)序列

A3：構造1到99且步長為3的整數(shù)序列

A4：創(chuàng)建數(shù)據(jù)結(jié)構為(jw,jm,jc)的序表

A5：對A1、A2、A3的數(shù)據(jù)進行嵌套循環(huán)，若滿足于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：

	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：創(chuàng)建數(shù)據(jù)結(jié)構為(k,f)的序表，并追加2條記錄(1, “a1:a2:a3)和(2,”b1:b2”)

A2：將A1的字段f用冒號劃分成序列并重新賦值給字段f

A3：針對A1每條記錄構造數(shù)據(jù)結(jié)構為(k,f)的序表，并根據(jù)字段f中成員構造記錄(A1.k，f成員)追加到此序表中

2、    生成連續(xù)日期序列

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：

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

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：

	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)

A4：A2中記錄按字段d的值對齊到A3

A5：根據(jù)A4和A3對位構造統(tǒng)計后的序表

3、    生成連續(xù)的工作日(不包含周六周日)序列

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：

	A
1	=workdays(date("2018-10-03"),   date("2018-10-09"))
2	=A1.new(~:d,day@w(~)-1:w)

       A1：構造從2018-10-03到2018-10-09不包含周六周日的日期序列

       A2：根據(jù)A1構造日期及相應周幾的序表

      

4、    根據(jù)序列生成表

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：

	A
1	=to(14).("a"/~)
2	=create(f1,f2,f3,f4).record(A1)

 

當前名稱：SQL難點解決：序列生成
鏈接分享：http://www.rwnh.cn/article24/jdjgje.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、網(wǎng)站設計、外貿(mào)網(wǎng)站建設、網(wǎng)站設計公司、網(wǎng)站建設、

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)