SQL 难点解决：特殊示例 _mysql

这一节我们对 SQL 和集算器 SPL 在序列值查找、分栏、动态行、动态列、指定序排序等方面进行了对比。
1、列出中文人口和英文人口均达到 1% 的国家代码
:
from world.
wherein ('', '') and >=1
group by
count(*)>=2;
集算器SPL:
=("mysql")
=A1.query@x("* from world. where >=1")
=A2.group()
=A3.(~.().("",""))
=A4.()
A4: 选取语言包含和的组

文章插图
2、从数据结构为 (id,v) 的表中，按 id 升序查找连续记录的 v 值分别为 23、7、11 时下一个记录的 v 值
:
with t(id,v) as ( 1,3 union all2,15
union all3,23 union all4,7
union all5,11 union all6,19
union all7,23 union all8,7
union all9,6),
s(v) as ( '23,7,11'),
t1(v) as ( (v order by id) from t),
t2(p1,p2,p3,next) as (
@p1:=(s.v,t1.v), @p2:=if(@p1>0,@p1+(s.v)+1,null),
@p3:=(',',t1.v,@p2),@s:=(t1.v,@p2,@p3-@p2)
from s,t1)
next from t2;
说明：利用串操作求下一个值，t中id为序号，v为值，s中v为待查的值串。
集算器SPL:
=("mysql")
=A1.query@x("witht(id,v) as ( 1,3 union all2,15 union all3,23 union all4,7 union all5,11 union all6,19 union all7,23union all8,7 union all9,6)* from t order by id")
[23,7,11]
=A2.(v)
=A4.pos@c(A3)
=if(A5>0,A4.m(A5+A3.len()))
A3: 待查值的序列
A5: 在A4中查找与A3成员连续相同的起始位置
3、在数据结构为 (id,used) 的表中，id 值连续， used 为 0 表示未用，为 1 时表示已用，请列出所有未用区间的起始和结束 id
MySQL:
with t(id,used) as ( 1,1 union all2,1
union all3,0 union all4,1
union all5,0 union all6,0
union all7,1 union all8,1
union all9,0 union all10,0
union all10,0 union all11,0),
first as ( a.id
from t a left join t b on a.id=b.id+1
where a.used=0 and (b.id is null or b.used=1)),
t2 as ( first.id , min(c.id) , max(d.id)
from first
left join t c on first.id
left join t d on first.id
group by )
, if( is null, (,), -1)
from t2;
说明：此SQL没有采用《SQL难点解决：直观分组》中用窗口函数将相邻的同值分到同组的思路，而是仅使用了普通的join和left join ， first求所有未用区间的起始id列表，t2求每个起始id对应的比它大的最小已用id和比它大的最大未用id，请读者仔细体会。
集算器SPL:
=("mysql")
=A1.query@x("witht(id,used) as ( 1,1 union all2,1 union all3,0 union all4,1 union all5,0 union all6,0 union all7,1union all8,1 union all9,0 union all10,0 union all10,0 union all11,0)* from t order by id")
=(,)
>A2.run(if(used==0&&used!=used[-1],a=id),if(used==0&&used!=used[1],A3.(0,a,id)))
A3：当 used 为 0 且和上一行 used 不等时当前行 id 即为起始 id，当 used 为 0 且和下一行 used 不等时则当前行 id 即为结束 id，并向 A3 中的插入

文章插图
4、分栏列出欧洲和非洲人口超 200 万的城市名称及人口（每栏按从多到少排序）
MySQL:
with t as ( t1.name,t1.,t2.,
rank()over( by t2. order by t1. desc) rk
from world.city t1 join world. t2 on t1.=t2.code
where t2. in ('','') and t1.>=
),
m(rk) as (rk from t)
t1.name ` City`, t1., t2.name ` City`, t2.
from m
left join ( * from t where ='') t1 using(rk)
left join ( * from t where ='') t2 using (rk);
集算器SPL:
=("mysql")
=A1.query@x("t1.name,t1.,t2. from world.city t1 join world. t2 ont1.=t2.code where t2. in ('','') andt1.>= order by t1. desc")
=A2.(:"")
=A2.(:"")
=('City',,' City', )
=A5.paste(A3.(name),A3.(),A4.(name),A4.())
A6：将值序列直接粘贴到对应列

文章插图
5、现有数据结构为 (,Math,,,, ,) 的成绩表，请列出低于 90 分的学科对应的所有学生的成绩
MySQL:
table
( (20),Math int, int, int,
int, int, int);
into
'Lili', 93,99,100,88,92,95
union all'', 100,99,97,100,85,96
union all'', 95,92,94,90,93,91
union all'', 97,89,92,99,98,88;
@m:=(if(Math
if(
if(
if(
if(
if(
from
where ='';
set @s:=left(@m, (@m)-1);
set @sql:=(' ,', @s, 'from ');
stmt from @sql;
stmt;
stmt;
drop table ;
集算器SPL:
=("mysql")
=A1.query@x("witht(,Math,,,, ,) as ( 'Lili', 93,99,100,88,92,95 union all '', 100,99,97,100,85,96union all '', 95,92,94,90,93,91 union all '',97,89,92,99,98,88)* from t")
=A2.@1(:"")
=A3.array().@a(#>1&&~
=A2.fname()(A4).@c()
=A2.new(,${A5})
A4：将记录转成数组，并查找低于90分的学科所在列号
A5：从A2中取出相应位置的列名，并且逗号分隔连在一起
A6：根据A2构造学生和选出的列的新序表

文章插图

文章插图
6、列出 2016 年 3 月各省市销售额，要求、、依次列在最前
MySQL:
*
from
where =
order by case when ='' then 1
when ='' then 2
when ='' then 3 else 4 end;
集算器SPL:
=("mysql")
=A1.query@x("* fromwhere =")
=["","",""]
=A2.align@s(A3,)
A4: 将A2中记录的按A3对齐，多余的按原序排在后面

文章插图
7、列出不存在人口超过 1000 的城市的国家
MySQL:
t1.code,t1.name
from world. t1
left join ( * from world.city where >=1000) t2
on t1.code=t2.
where t2. is null;
集算器SPL:
=("mysql")
=A1.query("code,name from world.")
=A1.query@xi("from world.city where >=1000")
【SQL 难点解决：特殊示例】=A2.@d(code,A3:)
A4：选取A2中code不在A3里的记录

文章插图