SQLZOO答案： 9- Window function_use partition to show the ranking of each party in

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
1
2
3
4

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
1
2
3
4
5

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
1
2
3
4
5

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
1
2
3
4
5
6

5. Show the parties that won for each Edinburgh constituency in 2017.

SELECT constituency,party FROM
(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) RK
WHERE RK.posn=1
1
2
3
4
5
6
7

6. Show how many seats for each party in Scotland in 2017.

SELECT party,COUNT(*) FROM (
SELECT constituency,party, votes,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) as posn
  FROM ge
 WHERE constituency LIKE 'S%'
   AND yr  = 2017) rk
WHERE rk.posn=1
GROUP BY rk.party
1
2
3
4
5
6
7
8

因为最近在写sql，网上找不到答案 就写了这篇文章，里面sql可能不是最优的，但是结果是没问题的。这是用CSDN以来写的第一篇CSDN文章，欢迎大家指正。如果大家有更优的sql，请不吝留言。