1、 對(duì)位分組
創(chuàng)新互聯(lián)公司專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù),包含不限于成都網(wǎng)站建設(shè)、成都做網(wǎng)站、蕭山網(wǎng)絡(luò)推廣、重慶小程序開(kāi)發(fā)、蕭山網(wǎng)絡(luò)營(yíng)銷、蕭山企業(yè)策劃、蕭山品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等,從售前售中售后,我們都將竭誠(chéng)為您服務(wù),您的肯定,是我們最大的嘉獎(jiǎng);創(chuàng)新互聯(lián)公司為所有大學(xué)生創(chuàng)業(yè)者提供蕭山建站搭建服務(wù),24小時(shí)服務(wù)熱線:028-86922220,官方網(wǎng)址:www.cdcxhl.com
示例 1:按順序分別列出使用 Chinese、English、French 作為官方語(yǔ)言的國(guó)家數(shù)量
MySQL8:
with t(name,ord) as (select 'Chinese',1
union all select 'English',2
union all select 'French',3)
select t.name, count(countrycode) cnt
from t left join world.countrylanguage s on t.name=s.language
where s.isofficial='T'
group by name,ord
order by ord;
注意:表的字符集和數(shù)據(jù)庫(kù)會(huì)話的字符集要保持一致。
(1) show variables like 'character_set_connection'查看當(dāng)前會(huì)話字符集
(2) show create table world.countrylanguage查看表的字符集
(3) set character_set_connection=[字符集]更新當(dāng)前會(huì)話字符集
集算器SPL:
A | |
1 | =connect("mysql") |
2 | =A1.query@x("select * from world.countrylanguage where isofficial='T'") |
3 | [Chinese,English,French] |
4 | =A2.align@a(A3,Language) |
5 | =A4.new(A3(#):name, ~.len():cnt) |
A1: 連接數(shù)據(jù)庫(kù)
A2: 查詢出所有官方語(yǔ)言的記錄
A3: 需要列出的語(yǔ)言
A4: 將所有記錄按Language對(duì)位到A3相應(yīng)位置
A5: 構(gòu)造以語(yǔ)言和使用此語(yǔ)言為官方語(yǔ)言的國(guó)家數(shù)量的序表
示例 2:按順序分別列出使用 Chinese、English、French 及其它語(yǔ)言作為官方語(yǔ)言的國(guó)家數(shù)量
MySQL8:
with t(name,ord) as (select 'Chinese',1 union all select 'English',2
union all select 'French',3 union all select 'Other', 4),
s(name, cnt) as (
select language, count(countrycode) cnt
from world.countrylanguage s
where s.isofficial='T' and language in ('Chinese','English','French')
group by language
union all
select 'Other', count(distinct countrycode) cnt
from world.countrylanguage s
where isofficial='T' and language not in ('Chinese','English','French')
)
select t.name, s.cnt
from t left join s using (name)
order by t.ord;
集算器SPL:
A | |
1 | =connect("mysql") |
2 | =A1.query@x("select * from world.countrylanguage where isofficial='T'") |
3 | [Chinese,English,French,Other] |
4 | =A2.align@an(A3.to(3),Language) |
5 | =A4.new(A3(#):name, if(#<=3,~.len(), ~.icount(CountryCode)):cnt) |
A4: 將所有記錄按Language對(duì)位到A3.to(3)相應(yīng)位置,并追加一組用于存放不能對(duì)位的記錄
A5: 第4組計(jì)算不同CountryCode的數(shù)量
2、 枚舉分組
示例 1:按順序列出各類型城市的數(shù)量
MySQL8:
with t as (select * from world.city where CountryCode='CHN'),
segment(class,start,end) as (select 'tiny', 0, 200000
union all select 'small', 200000, 1000000
union all select 'medium', 1000000, 2000000
union all select 'big', 2000000, 100000000
)
select class, count(1) cnt
from segment s join t on t.population>=s.start and t.population group by class, start order by start; 集算器SPL: A3: ${…}宏替換,以大括號(hào)內(nèi)表達(dá)式的結(jié)果作為新表達(dá)式進(jìn)行計(jì)算,結(jié)果為序列["?<200000","?<1000000","?<2000000","?<100000000"] A5: 針對(duì) A2 中每條記錄,尋找 A3 中第 1 個(gè)成立的條件,并追加到對(duì)應(yīng)的組中 示例 2:列出華東地區(qū)大型城市數(shù)量、其它地區(qū)大型城市數(shù)量、非大型城市數(shù)量 MySQL8: with t as (select * from world.city where CountryCode='CHN') select 'East&Big' class, count(*) cnt from t where population>=2000000 and district in ('Shanghai','Jiangshu', 'Shandong','Zhejiang','Anhui','Jiangxi') union all select 'Other&Big', count(*) from t where population>=2000000 and district not in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi') union all select 'Not Big', count(*) from t where population<2000000; 集算器SPL: A5: enum@n將不滿足 A4 中所有條件的記錄存放到追加的最后一組中 示例 3:列出所有地區(qū)大型城市數(shù)量、華東地區(qū)大型城市數(shù)量、非大型城市數(shù)量 MySQL8: with t as (select * from world.city where CountryCode='CHN') select 'Big' class, count(*) cnt from t where population>=2000000 union all select 'East&Big' class, count(*) cnt from t where population>=2000000 and district in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi') union all select 'Not Big' class, count(*) cnt from t where population<2000000; 集算器SPL: A6: 若A2中記錄滿足A4中多個(gè)條件時(shí),enum@r會(huì)將其追加到對(duì)應(yīng)的每個(gè)組中 3、 返回值直接作為序號(hào)進(jìn)行定位分組 示例 1: 按順序列出各類型城市的數(shù)量 MySQL8: 參見(jiàn)“枚舉分組”中 SQL 集算器SPL: A5: 先計(jì)算 A2.Population 在 A3 中段號(hào),然后根據(jù)段號(hào)進(jìn)行定位分組 4、 原序保持下的相鄰記錄分組 示例 1: 列出前 10 屆奧運(yùn)金牌榜 (olympic 表中只有歷屆成績(jī)前 3 名的信息,且沒(méi)有獎(jiǎng)牌完全相同的情況) MySQL8: with t1 as (select *,rank() over(partition by game order by gold*1000000+silver*1000+copper desc) rn from olympic where game<=10) select game,nation,gold,silver,copper from t1 where rn=1; 集算器SPL: A3: 按原序分到各組,每組取第 1 條記錄組成新序表 示例 2: 求奧運(yùn)會(huì)國(guó)家總成績(jī)蟬聯(lián)第 1 的最長(zhǎng)屆數(shù) MySQL8: with t1 as (select *,rank() over(partition by game order by gold*1000000+silver*1000+copper desc) rn from olympic), t2 as (select game,ifnull(nation<>lag(nation) over(order by game),0)neq from t1 where rn=1), t3 as (select sum(neq) over(order by game) acc from t2), t4 as (select count(acc) cnt from t3 group by acc) select max(cnt) cnt from t4; t1: 求出成績(jī)排名 t2: 列出歷屆第1名,并根據(jù)nation是否與上屆不同置標(biāo)志neq(不同置1,相同置0) t3: 累積標(biāo)志neq到acc,可以保證相鄰nation相同的acc相同,不相鄰nation的acc不相同 集算器SPL: A4: 將相鄰nation相同的記錄按原序分到同組 A5: 求各組長(zhǎng)度的最大值即最大屆數(shù) 示例3:列出奧運(yùn)會(huì)總成績(jī)排名第一最長(zhǎng)蟬聯(lián)時(shí)的各屆信息 MySQL: with t1 as (select *,rank() over(partition by game order by gold*1000000+silver*1000+copper desc) rn from olympic), t2 as (select *,ifnull(nation<>lag(nation) over(order by game),0)neq from t1 where rn=1), t3 as (select *, sum(neq) over(order by game) acc from t2), t4 as (select acc,count(acc) cnt from t3 group by acc), t5 as (select * from t4 where cnt=(select max(cnt) cnt from t4)) select game,nation,gold,silver,copper from t3 join t5 using (acc); 集算器SPL: A5: 求出長(zhǎng)度最大組 示例 4:求奧運(yùn)會(huì)前3名金牌總數(shù)連續(xù)增長(zhǎng)的最大屆數(shù) MySQL8: with t1 as (select game,sum(gold) gold from olympic group by game), t2 as (select game,gold, gold<=lag(gold,1,-1) over(order by game) lt from t1), t3 as (select game, sum(lt) over(order by game) acc from t2), t4 as (select count(*) cnt from t3 group by acc) select max(cnt)-1 cnt from t4; 集算器SPL: A3: 根據(jù)條件值按原序分組,若gold小于等于上一個(gè)gold則產(chǎn)生新分組A 1 =connect("mysql") 2 =A1.query@x("select * from world.city where CountryCode='CHN'") 3 =${string([20,100,200,10000].(~*10000).("?<"/~))} 4 [tiny,small,medium,big] 5 =A2.enum(A3,Population) 6 =A5.new(A4(#):class, ~.len():cnt) A 1 =connect("mysql") 2 =A1.query@x("select * from world.city where CountryCode='CHN'") 3 [Shanghai,Jiangshu, Shandong,Zhejiang,Anhui,Jiangxi] 4 [?(1)>=2000000 && A3.contain(?(2)), ?(1)>=2000000 && !A3.contain(?(2))] 5 [East&Big,Other&Big, Not Big] 6 =A2.enum@n(A4, [Population,District]) 7 =A6.new(A5(#):class, A6(#).len():cnt) A 1 =connect("mysql") 2 =A1.query@x("select * from world.city where CountryCode='CHN'") 3 [Shanghai,Jiangshu, Shandong,Zhejiang,Anhui,Jiangxi] 4 [?(1)>=2000000, ?(1)>=2000000 && A3.contain(?(2))] 5 [Big, East&Big, Not Big] 6 =A2.enum@rn(A4, [Population,District]) 7 =A6.new(A5(#):class, A6(#).len():cnt) A 1 =connect("mysql") 2 =A1.query@x("select * from world.city where CountryCode='CHN'") 3 =[0,20,100,200].(~*10000) 4 [tiny,small,medium,big] 5 =A2.group@n(A3.pseg(Population)) 6 =A5.new(A4(#):class, ~.len():cnt) A 1 =connect("mysql") 2 =A1.query("select * from olympic where game<=10 order by game, gold*1000000+silver*1000+copper desc") 3 =A2.group@o1(game) A 1 =connect("mysql") 2 =A1.query("select * from olympic order by game, gold*1000000+silver*1000+copper desc") 3 =A2.group@o1(game) 4 =A3.group@o(nation) 5 =A4.max(~.len()) A 1 =connect("mysql") 2 =A1.query("select * from olympic order by game, gold*1000000+silver*1000+copper desc") 3 =A2.group@o1(game) 4 =A3.group@o(nation) 5 =A4.maxp(~.len()) A 1 =connect("mysql") 2 =A1.query("select game,sum(gold) gold from olympic group by game order by game") 3 =A2.group@i(gold<=gold[-1]) 4 =A3.max(~.len())-1
當(dāng)前名稱:SQL難點(diǎn)解決:直觀分組
瀏覽地址:http://weahome.cn/article/gjssio.html