前言
SQLZOO里面的题还是比较适合初学者的,这里仅仅作为巩固基础,同时因为这道题目前搜不到相应的答案,所以做个小小的分享~
PS:非小白玩家可以去LeetCode、牛客网、CodeWars上刷题更佳
题目
General Elections were held in the UK in 2015 and 2017. Every citizen votes in a constituency. The candidate who gains the most votes becomes MP for that constituency.
All these results are recorded in a table ge
yr | firstName | lastName | constituency | party | votes |
---|---|---|---|---|---|
2015 | Ian | Murray | S14000024 | Labour | 19293 |
2015 | Neil | Hay | S14000024 | Scottish National Party | 16656 |
2015 | Miles | Briggs | S14000024 | Conservative | 8626 |
2015 | Phyl | Meyer | S14000024 | Green | 2090 |
2015 | Pramod | Subbaraman | S14000024 | Liberal Democrat | 1823 |
2015 | Paul | Marshall | S14000024 | UK Independence Party | 601 |
2015 | Colin | Fox | S14000024 | Scottish Socialist Party | 197 |
2017 | Ian | MURRAY | S14000024 | Labour | 26269 |
2017 | Jim | EADIE | S14000024 | SNP | 10755 |
2017 | Stephanie Jane Harley | SMITH | S14000024 | Conservative | 9428 |
2017 | Alan Christopher | BEAL | S14000024 | Liberal Democrats | 1388 |
1.Show the lastName, party and votes for the constituency ‘S14000024’ in 2017.
SELECT
lastName,
party,
votes
FROM
ge
WHERE
constituency = 'S14000024' AND yr = 2017
ORDER BY votes DESC
2.Show the party and RANK for constituency S14000024 in 2017. List the output by party
SELECT
party,
votes,
RANK() OVER (ORDER BY votes DESC) as posn
FROM
ge
WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY party
3.Use PARTITION to show the ranking of each party in S14000021 in each year. Include yr, party, votes and ranking (the party with the most votes is 1).
SELECT
yr,
party,
votes,
RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
FROM
ge
WHERE constituency = 'S14000021'
ORDER BY party,yr
4.Use PARTITION BY constituency to show the ranking of each party in Edinburgh in 2017. Order your results so the winners are shown first, then ordered by constituency.
SELECT
constituency,
party,
votes,
RANK() over(PARTITION BY constituency ORDER BY votes DESC) as posn
FROM
ge
WHERE
constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
ORDER BY posn, constituency
5.Show the parties that won for each Edinburgh constituency in 2017.
SELECT
a.constituency,
a.party
FROM
(SELECT
constituency,
party,
RANK() over(partition by constituency order by votes DESC) as posn
FROM
ge
WHERE
constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017) a
WHERE a.posn = 1
6.Show how many seats for each party in Scotland in 2017.
# 这道题想了一会儿
SELECT
a.party,
count(1)
FROM
(SELECT
constituency,
party,
RANK() over(partition by constituency order by votes DESC) as posn
FROM
ge
WHERE
constituency like 'S%'
AND yr = 2017) a
WHERE a.posn =1
GROUP BY a.party