我最近參加了一個關于 SQL 的采訪。下面是問題：
撰寫查詢以檢索城市名稱、州名稱、城市人口，并回傳以下計算以及前面提到的欄位。
- 該州的平均城市人口。
- 該州城市人口與平均城市人口之間的差異。
城市表：
| 城市名稱 | 狀態_名稱 | 人口 |
|---|---|---|
| 巴爾的摩 | 馬里蘭 | 30000 |
| 大學公園 | 馬里蘭 | 18000 |
| 哥倫比亞 | 馬里蘭 | 20000 |
| 波士頓 | 馬薩諸塞州 | 35000 |
| 馬爾登 | 馬薩諸塞州 | 10000 |
| 多佛 | 特拉華州 | 20000 |
| 澤西城 | 新澤西州 | 35000 |
我嘗試了以下查詢，但沒有得到想要的輸出。誰能幫我正確查詢？
select * from city_table;
select state_name, sum(population)/count(city_name) as average_city_pop
from city_table
group by state_name;
uj5u.com熱心網友回復：
您需要更多地探索 sql，了解聚合函式和連接。您的查詢在獲得平均人口方面是正確的。您需要將其加入原始表才能獲得結果。
with avg_city_pop as (select state_name, avg(population) as avg from city group by state_name)
select c.*, acp.avg as average_city_population, abs(acp.avg-c.population) as difference from city c inner join avg_city_pop acp on c.state_name = acp.state_name;
在這里試試
uj5u.com熱心網友回復：
對于 MySQL 8.0 之前的版本，我們可以使用相關子查詢來獲取每個州的平均人口，這也可以用來計算城市人口與州平均人口之間的差距。請注意，該abs函式可選擇用于始終回傳一個正數。必要時省略它。
select city_name,state_name,(select avg(population) from city where state_name=c.state_name group by state_name ) as average_pop ,
abs(population - (select avg(population) from city where state_name=c.state_name group by state_name )) as gap_pop
from city c;
uj5u.com熱心網友回復：
select
city_name,
state_name,
population,
avg(population) over (partition by state_name) as Avg_pop,
population-avg(population) over (partition by state_name) as Difference
from city;
見 sqlfilldle：https ://www.db-fiddle.com/f/tVxdH6bQvsu48umWgLmrQB/0
這需要 MySQL 8.0 ，因為over_clause從 8.0 開始可用。
輸出：
| 城市名稱 | 州名 | 人口 | 平均流行音樂 | 不同之處 |
|---|---|---|---|---|
| 多佛 | 特拉華州 | 20000 | 20000.0000 | 0.0000 |
| 巴爾的摩 | 馬里蘭 | 30000 | 22666.6667 | 7333.3333 |
| 大學公園 | 馬里蘭 | 18000 | 22666.6667 | -4666.6667 |
| 哥倫比亞 | 馬里蘭 | 20000 | 22666.6667 | -2666.6667 |
| 波士頓 | 馬薩諸塞州 | 35000 | 22500.0000 | 12500.0000 |
| 模具 | 馬薩諸塞州 | 10000 | 22500.0000 | -12500.0000 |
| 澤西城 | 新澤西州 | 35000 | 35000.0000 | 0.0000 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/467568.html