簡(jiǎn)單的SELECT 字段 FROM 表 WHERE 條件 GROUP BY 字段相信應(yīng)該不用介紹了,很多基礎(chǔ)教程都有寫,本篇主要講幾個(gè)結(jié)合一些語(yǔ)法的sql語(yǔ)句例子
AGG_FUNC()
例如count(*),sum(),min(),max(),avg()
HAVING語(yǔ)句
數(shù)據(jù)庫(kù)是先對(duì)數(shù)據(jù)做WHERE梗肝，然后對(duì)結(jié)果做group by分組韭脊，在分組后再進(jìn)行篩選應(yīng)該使用having (其實(shí)having和where一樣,只是作用的結(jié)果集不一樣)
完整的查詢語(yǔ)句
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
JOIN another_table
ON mytable.column = another_table.column
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC/DESC
LIMIT count OFFSET COUNT;
他的執(zhí)行順序是
- from和join先執(zhí)行,確定你要查詢的表的范圍
- where在這個(gè)表范圍中所有行進(jìn)行篩選
- group by對(duì)where篩選后剩下的行進(jìn)行分組
- having可以對(duì)group by后的組進(jìn)行篩選
- 確定好最終范圍后select選擇要查詢的字段
- 如果有重復(fù),可以用distinct來(lái)去重
- 字段結(jié)果確定后,order by來(lái)排序
- 最終用limit和offset來(lái)截取部分?jǐn)?shù)量/位置的數(shù)據(jù)
下面開始舉例
首先看我們的數(shù)據(jù)集
兩張表
1.統(tǒng)計(jì)出每一個(gè)導(dǎo)演的電影數(shù)量(列出導(dǎo)演名字和數(shù)量）
SELECT director,count(*) FROM movies GROUP BY director
2.統(tǒng)計(jì)一下每個(gè)導(dǎo)演的銷售總額(列出導(dǎo)演名字和銷售總額)
SELECT director, sum(Domestic_sales+International_sales) as total_sale
FROM movies
LEFT JOIN boxoffice
ON movies.id = boxoffice.movie_id
GROUP BY director
3.按導(dǎo)演分組計(jì)算銷售總額,求出平均每部電影銷售額冠軍(統(tǒng)計(jì)結(jié)果過(guò)濾掉只有單部電影的導(dǎo)演，列出導(dǎo)演名，總銷量凫乖，電影數(shù)量，平均電影銷量)
SELECT director
,sum(Domestic_sales+International_sales) as sum_sale
,count(*) as count
,sum(Domestic_sales+International_sales)/count(*) as avg_sale
FROM movies
LEFT JOIN boxoffice
ON movies.id = boxoffice.movie_id
GROUP BY director
HAVING count > 1
ORDER BY avg_sale DESC
LIMIT 1
4.找出單部電影銷售額冠軍和其他每部電影之間的銷售差(列出電影名操漠，銷售額差額)
SELECT title,
(
SELECT max(Domestic_sales+International_sales) as total_sale
FROM movies
LEFT JOIN boxoffice
ON movies.id = boxoffice.movie_id
)
- (Domestic_sales+International_sales) as sale_diff
FROM movies
LEFT JOIN boxoffice
ON movies.id = boxoffice.movie_id
ORDER BY sale_diff desc
按照上面的語(yǔ)法查詢順序來(lái)梳理
- 首先確定范圍是FROM movies
LEFT JOIN boxoffice ON movies.id = boxoffice.movie_id
得到完整查詢范圍join后的表.png - 因?yàn)榍蟮氖敲坎侩娪?所以不需要where篩選,group by分組和having對(duì)組篩選
- 選擇字段,先
select title - 接下來(lái)是每部電影和單部電影均銷售額冠軍之間的銷售差這個(gè)字段
- 單部電影銷冠
SELECT max(Domestic_sales+International_sales) as total_sale FROM movies LEFT JOIN boxoffice ON movies.id = boxoffice.movie_id是一個(gè)常數(shù) - 與每部電影銷量
Domestic_sales+International_sales相減得到銷售差
結(jié)果示例
