链接:https://sqlzoo.net/wiki/Window_functions
SELECT lastName, party, votes
FROM ge
WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY votes DESC
SELECT party, votes,
RANK() OVER (ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY party
SELECT yr,party, votes,
RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000021'
ORDER BY party,yr
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
SELECT TOP 6 constituency,party
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026' AND yr = 2017
ORDER BY RANK() OVER(PARTITION BY constituency ORDER BY votes DESC), constituency
SELECT x.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
)AS x
WHERE x.posn=1
GROUP BY x.party