sql相關

基礎語句：

模糊匹配：
% 任意字符，_占位符
like '_t%';

多字段排序:
select winner,yr,subject from nobel where winner like 'Sir%' order by yr desc,winner asc
按照特殊排序，把chemistry,physics排在后面
select winner,subject from nobel where yr = 1984 order by subject in ('chemistry','physics') ,subject, winner
limit限制范圍：

select name,population from world
order by population desc
limit 3,4

sg:從第4行到第7行的數據

聚合函數忽略空值

某個字段包含另外字段 可以用concat+like進行篩選

高級語句：
窗口函數、子查詢、表鏈接
窗口函數相當于把having后面的數據拿去新窗口操作再返回到原表，不影響原表排序
over([partition by xxx] order by xxx)
只能寫在select里面,而且分區不去重

sg:
SELECT name, DATE_FORMAT(whn,'%Y-%m-%d'), confirmed,lag(confirmed,1)over(partition by name order by whn),(confirmed-lag(confirmed,1)over(partition by name order by whn)) new1 FROM covid WHERE name = 'Italy' AND WEEKDAY(whn) = 0 ORDER BY whn
計算一周的新增人數 = 這下周（周一）減這一周周一 間隔7天

表連接（基于廣義迪卡爾積篩選）：
內連接 from 表1 join 表2 on 字段名=字段名
左右連接 left join and right join

SELECT mdate,team1,sum(case when team1=teamid then 1 else 0 end) score1 ,team2,sum(case when team2=teamid then 1 else 0 end) score2 from game ga left join goal go on ga.id=go.matchid group by mdate,team1,team2 order by mdate,matchid, team1 and team2
統計同場比賽球隊的進球情況

子查詢：
from 子查詢要起別名

sg:select name,continent,population from world where continent not in ( select distinct continent from world where population>25000000)

select name,continent,population from world where continent in( select continent from world group by continent having max(population)<= 25000000)
求洲里面所有國家的人口數小于等于25000000，只要排除洲里面包含的國家有超過這個數的就好

`select continent,name,area
from(
select continent,name,
area,rank()over(partition by continent order by area desc) position
from world
order by name) as r

where r.position =1 select continent,name,area
from world
where (continent,area) in (
select continent,max(area)
from world
group by continent
)
`
查詢每個洲里面面積最大的國家