Solving the difficulty of SQL: intuitive grouping
1. Parity grouping
Example 1: Number of countries using Chinese, English, French as official languages in order
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;
Note: Keep the character set of the table consistent with the character set of the database session.
(1)show variables like 'character_set_connection' View the current session character set
(2)show create table world.countrylanguage View table character set
(3)set character_set_connection=[character set] Update the character set of the current session
Concentrator SPL:
A1=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: connect to database
A2: Find records in all official languages
A3: Languages to be listed
A4: Align all records to the corresponding position of A3 according to Language
A5: Construct an order table by language and the number of countries that use that language as an official language
Example 2: Number of countries using Chinese, English, French, and other languages as official languages in order
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;
Concentrator SPL:
A1=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(#=s.start and t.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 && A3.contain(? (2)), ? (1)>=2000000 && ! A3.contain(? (2))]5[East&Big,Other&Big, Not Big]6= A2.enumer@n (A4, [Population,District])7=A6.new(A5(#):class, A6(#).len():cnt)A5: enum@ nStore records that do not satisfy all conditions in A4 in the last group added
Example 3: List the number of large cities in all regions, the number of large cities in East China, and the number of non-large cities
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, ? (1)>=2000000 && A3.contain(? (2))]5[Big, East&Big, Not Big]6= A2.enumer@rn (A4, [Population,District])7=A6.new(A5(#):class, A6(#).len():cnt)A6: If a record in A2 satisfies multiple conditions in A4, enum@r appends it to each corresponding group
3. The returned value is directly used as the serial number for positioning grouping.
Example 1: Number of cities in order
MySQL8: See SQL in "Enumeration grouping"
Concentrator SPL:
A1=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)A5: First calculate the middle number of A2.Population in A3, and then locate and group according to the segment number.
4. Adjacent record grouping under original order preservation
Example 1: List the top 10 Olympic gold medals (the Olympic table only has information about the top 3 of all previous results, and no medals are exactly the same)
MySQL8:
with t1 as (select *,rank() over(partition by game order by gold*1000000+silver*1000+copper desc) rn from olympic where game
