SQL 難點解決：特殊示例

這一節我們對 SQL 和集算器 SPL 在序列值查找、分欄、動態行、動態列、指定序排序等方面進行了對比。

1、    列出中文人口和英文人口均達到 1% 的國家代碼

MySQL8:

select countrycode from world.countrylanguage

where language in ('Chinese', 'English') and percentage>=1

group by countrycode

having count(*)>=2;

集算器SPL:

A4: 選取語言包含 Chinese 和 English 的組

2、    從數據結構為 (id,v) 的表中，按 id 升序查找連續記錄的 v 值分別為 23、7、11 時下一個記錄的 v 值

MySQL8:

with t(id,v) as (select 1,3 union all select 2,15

union all select 3,23 union all select 4,7

union all select 5,11 union all select 6,19

union all select 7,23 union all select 8,7

union all select 9,6),

s(v) as (select '23,7,11'),

t1(v) as (select group_concat(v order by id) from t),

t2(p1,p2,p3,next) as (

select @p1:=locate(s.v,t1.v), @p2:=if(@p1>0,@p1+char_length(s.v)+1,null),

@p3:=locate(',',t1.v,@p2),@s:=substr(t1.v,@p2,@p3-@p2)

from s,t1)

select next from t2;

說明：利用串操作求下一個值，t中id為序號，v為值，s中v為待查的值串。

集算器SPL:

A3: 待查值的序列

A5: 在A4中查找與A3成員連續相同的起始位置

3、    在數據結構為 (id,used) 的表中，id 值連續，used 為 0 表示未用，為 1 時表示已用，請列出所有未用區間的起始和結束 id

MySQL:

with t(id,used) as (select 1,1 union all select 2,1

union all select 3,0 union all select 4,1

union all select 5,0 union all select 6,0

union all select 7,1 union all select 8,1

union all select 9,0 union all select 10,0

union all select 10,0 union all select 11,0),

first as (select 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 (select first.id firstUnused, min(c.id) minUsed, max(d.id) maxUnused

from first

left join t c on first.id<c.id and c.used=1

left join t d on first.id<d.id and d.used=0

group by firstUnused)

select firstUnused, if(minUsed is null, ifnull(maxUnused,firstUnused), minUsed-1) lastUnused

from t2;

說明：此SQL沒有采用《SQL難點解決：直觀分組》中用窗口函數將相鄰的同值分到同組的思路，而是僅使用了普通的join和left join，first求所有未用區間的起始id列表，t2求每個起始id對應的比它大的最小已用id和比它大的最大未用id，請讀者仔細體會。

集算器SPL:

A3：當 used 為 0 且和上一行 used 不等時當前行 id 即為起始 id，當 used 為 0 且和下一行 used 不等時則當前行 id 即為結束 id，并向 A3 中的插入

4、    分欄列出歐洲和非洲人口超 200 萬的城市名稱及人口（每欄按從多到少排序）

MySQL:

with t as (select t1.name,t1.population,t2.continent,

rank()over(partition by t2.continent order by t1.population desc) rk

from world.city t1 join world.country t2 on t1.countrycode=t2.code

where t2.continent in ('Europe','Africa') and t1.population>=2000000

),

m(rk) as (select distinct rk from t)

select t1.name `Europe City`, t1.Population, t2.name `Africa City`, t2.Population

from m

left join (select * from t where continent='Europe') t1 using(rk)

left join (select * from t where continent='Africa') t2 using (rk);

集算器SPL:

A6：將值序列直接粘貼到對應列

5、    現有數據結構為 (Student,Math,Chinese,English,Physics, Chemistry,Information) 的成績表，請列出 Maliang 低于 90 分的學科對應的所有學生的成績

MySQL:

create temporary table

scores(Student varchar(20),Math int,Chinese int,English int,

Physics int,Chemistry int,Information int);

insert into scores

select 'Lili', 93,99,100,88,92,95

union all select 'Sunqiang', 100,99,97,100,85,96

union all select 'Zhangjun', 95,92,94,90,93,91

union all select 'Maliang', 97,89,92,99,98,88;

select @m:=concat(if(Math<90, 'Math,', ''),

if(Chinese<90, 'Chinese,', ''),

if(English<90, 'English,', ''),

if(Physics<90, 'Physics,', ''),

if(Chemistry<90, 'Chemistry,', ''),

if(Information<90, 'Information,', ''))

from scores

where student='Maliang';

set @s:=left(@m, length(@m)-1);

set @sql:=concat('select Student,', @s, 'from scores');

prepare stmt from @sql;

execute stmt;

deallocate prepare stmt;

drop table scores;

集算器SPL:

A4：將記錄轉成數組，并查找低于90分的學科所在列號

A5：從A2中取出相應位置的列名，并且逗號分隔連在一起

A6：根據A2構造學生和選出的列的新序表

6、    列出 2016 年 3 月各省市銷售額，要求 Beijing、Shanghai、Guangdong 依次列在最前

MySQL:

select *

from detail

where yearmonth=201603

order by case when province='Beijing' then 1

when province='Shanghai' then 2

when province='Guangdong' then 3 else 4 end;

集算器SPL:

A4: 將A2中記錄的province按A3對齊，多余的按原序排在后面

7、    列出不存在人口超過 1000 的城市的國家

MySQL:

select t1.code,t1.name

from world.country t1

left join (select * from world.city where population>=1000) t2

on t1.code=t2.countrycode

where t2.countrycode is null;

集算器SPL:

A4：選取A2中code不在A3里的記錄