SQLZOO Window functions

最新推荐文章于 2026-01-05 09:36:16 发布

原创最新推荐文章于 2026-01-05 09:36:16 发布 · 273 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

sql 专栏收录该内容

6 篇文章

订阅专栏

链接：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