SQL 难点解决之集合及行号

可直接阅读原文：http://c.raqsoft.com.cn/article/1542508403539?r=alice

1、  和集

示例 1：求重叠时间段的总天数

MySQL8:

with recursive t(start,end) as (select date'2010-01-07',date'2010-01-9'

union all select date'2010-01-15',date'2010-01-16'

union all select date'2010-01-07',date'2010-01-12'

union all select date'2010-01-08',date'2010-01-11'),

t1(d,end) as (select start,end from t

union all select d+1,end from t1 where d

select count(distinct d) from t1;

说明：此例先将各时间段转成时间段内所有日子对应的日期，然后再求不同日期的个数

集算器SPL:

	A
1	=connect("mysql")
2	=A1.query("select   CountryCode,Name,Language,Percentage from world.countrylanguage cl join   world.country c on cl.countrycode=c.code where percentage>5")
3	=A2.group(CountryCode)
4	=A3.select(["English","French"]\~.(Language)==[])
5	=A4.new(~.Name:name)

A4: 选出[“English”,”French”]与本组语言集合的差为空的组，意思就是选出语言集合包含English和French的组

2、  差集

示例 1：列出英语人口和法语人口均超过 5% 的国家

MySQL8:

with t1(lang) as (select 'English' union all select 'French')

select name from world.country c

where not exists(select * from t1 where lang not in (select language from world.countrylanguage where percentage>=5 and countrycode=c.code));

说明：此SQL只是演示通过双重否定实现差集为空

 

3、  交集

示例 1：列出英语人口、法语人口、西班牙语人口分别超过 0.3%、0.2%、0.1% 的国家代码

MySQL8:

with t1 as (select countrycode from world.countrylanguage where language='English' and percentage>0.3),

t2 as (select countrycode from world.countrylanguage where language='French' and percentage>0.2),

t3 as (select countrycode from world.countrylanguage where language='Spanish' and percentage>0.1)

select countrycode

from t1 join t2 using(countrycode) join t3 using(countrycode);

说明：此例只是演示如何求解多个集合的交集

 

集算器SPL:

	A
1	=connect("mysql")
2	[English,French,Spanish]
3	[0.3,0.2,0.1]
4	=A2.(A1.query@i("select   countrycode from world.countrylanguage where language=? and   percentage>?",~,A3(#)))
5	>A1.close()
6	=A4.isect()

A3: 按次序依次查询英语人口超0.3%、法语人口超0.2%、西班牙语超0.1%的国家代码，并转成序列

A5: A3中所有序列交集

具体的行号可看原文实现方式：http://c.raqsoft.com.cn/article/1542508403539?r=alice

作者：zaoya
来源：乾学院
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。