高级语句
窗口函数
排序窗口函数
例题二十九
select yr,party,votes,
rank() over (PARTITION BY yr ORDER BY votes desc) as pson
from ge
where constituency = 'S14000021'
order by party,yr偏移分析函数
例题三十
select name,date_format(whn,'%Y-%m-%d') data,
confirmed 当天截止时间累计确诊人数,
lag(confirmed,1) over (partition by name order by whn) 昨天截止时间累计确诊人数,
(confirmed - lag(confirmed,1) over (partition by name order by whn)) 每天新增确诊人数
from covid
where name in ('France','Germany') and month(whn) = 1
order by whn总结
练习题
1
select party,votes,
rank() over (order by votes desc) as pson
from ge
where constituency = 'S14000024' and yr = 2017
order by party
2
SELECT name,confirmed AS 确诊人数,RANK() OVER (ORDER BY confirmed DESC) AS 确诊人数排名,deaths AS 死亡人数,RANK() OVER (ORDER BY deaths DESC) AS 死亡人数排名
FROM covid
WHERE whn = '2020-04-20'
ORDER BY confirmed DESC;3
select name,date_format(whn,'%Y-%m-%d') date,
(confirmed - lag(confirmed,1) over (order by whn)) New
from covid
where name = 'Italy' and WEEKDAY(whn) = 0
order by whn表连接
inner join
left join
right join
例题三十一
例题三十二
例题三十三
总结
练习题
1
2
3
