Shulou(Shulou.com)06/03 Report--

In this section, we compare SQL with the aggregator SPL in serial value lookups, columns, dynamic rows, dynamic columns, specified ordering, and so on.

1. List the country codes where both the Chinese population and the English population reach 1%

MySQL8:

Select countrycode from world.countrylanguage

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

Group by countrycode

Having count (*) > = 2

Aggregator SPL:

A1=connect ("mysql") 2=A1.query@x ("select * from world.countrylanguage where percentage > = 1") 3=A2.group (CountryCode) 4=A3.select (~. (Language) .contain ("Chinese", "English")) 5=A4. (CountryCode) A4: select the group whose language contains Chinese and English

2. From the table with data structure (id,v), find the v value of the next record when the v value of the continuous record is 23, 7 and 11 respectively in the ascending order of id.

MySQL8:

With t (id,v) as (select 1 union all select 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 pr 6)

S (v) as (select'23 ~ (7) ~ 11')

T1 (v) as (select group_concat (v order by id) from t)

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

Select @ p1:=locate (s.vret 1.v), @ p2:=if (@ p1 > 0recorder p1century chartered length (s.v) + 1dint null)

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

From spencil T1)

Select next from t2

Note: use string operation to find the next value, id in t is the serial number, v is the value, and v is the value string to be checked.

Aggregator SPL:

A1=connect ("mysql") 2=A1.query@x ("with t (id,v) as (select 1 union all select 2) 15 union all select 23 union all select 4 7 union all select 5 union all select 11 union all select 6 19 union all select 7 union all select 8 Japanese 7 union all select 9) select * from t order by id") 3 [23 Japanese 7 select 11] 4=A2. (v) 5=A4.pos@c (A3) 6=if (A5 > 0pr A4.m (A5+A3.len ()) A3: the sequence of values to be checked.

A5: find the same continuous starting position as the A3 member in A4

3. In a table with a data structure of (id,used), the values of id are continuous. A value of 0 means unused, and a value of 1 means used. List the start and end id of all unused intervals.

MySQL:

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

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 10dint 0 union all select 11je 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=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)

Aggregator SPL:

A1=connect ("mysql") 2=A1.query@x ("select t1.namediary t1.populationdagendary T2 on t1.countrycode=t2.code where t2.continent in ('Europe','Africa') and t1.population > = 2000000 order by t1.population desc") 3=A2.select (continent: "Europe") 4=A2.select (continent: "Africa") 5=create (' Europe City',population,'Africa City',population) 6=A5.paste (A3. (name), A3. (population), A4. (name) A4. (population)) A6: paste the value sequence directly into the corresponding column

5. For the score tables with existing data structure (Student,Math,Chinese,English,Physics, Chemistry,Information), please list the scores of all students in subjects with Maliang below 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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.