SQL 难点解决：特殊示例
这一节我们对 SQL 和集算器 SPL 在序列值查找、分栏、动态行、动态列、指定序排序等方面进行了对比。
1、 列出中文人口和英文人口均达到 1% 的国家代码
:
from world.
where in ('', '') 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 all 2,15
union all 3,23 union all 4,7
union all 5,11 union all 6,19
union all 7,23 union all 8,7
union all 9,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("with t(id,v) as ( 1,3 union all 2,15 union all 3,23 union all 4,7 union all 5,11 union all 6,19 union all 7,23 union all 8,7 union all 9,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 all 2,1
union all 3,0 union all 4,1
union all 5,0 union all 6,0
union all 7,1 union all 8,1
union all 9,0 union all 10,0
union all 10,0 union all 11,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("with t(id,used) as ( 1,1 union all 2,1 union all 3,0 union all 4,1 union all 5,0 union all 6,0 union all 7,1 union all 8,1 union all 9,0 union all 10,0 union all 10,0 union all 11,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 on t1.=t2.code where t2. in ('','') and t1.>= 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("with t(,Math,,,, ,) as ( '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) * 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(" * from where =")
=["","",""]
=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")
=A2.@d(code,A3:)
A4：选取A2中code不在A3里的记录
