sql常见开窗函数及相应举例
时间: 2023-05-26 17:01:45 浏览: 102
A:SQL中常见的窗口函数包括:
1. ROW_NUMBER():返回每行数据的行号,类似于自增ID。
举例:
```
SELECT name, grade, ROW_NUMBER() OVER(ORDER BY grade DESC) AS rank FROM students;
```
2. RANK():计算并返回每行数据在结果集中的排名,相同值则排名相同,排名相邻的不会有间隔。
举例:
```
SELECT name, grade, RANK() OVER(ORDER BY grade DESC) AS rank FROM students;
```
3. DENSE_RANK():计算并返回每行数据在结果集中的排名,相同值则排名相同,但排名相邻的之间会有间隔。
举例:
```
SELECT name, grade, DENSE_RANK() OVER(ORDER BY grade DESC) AS rank FROM students;
```
4. LAG() / LEAD():分别返回前一个或后一个行数据的值。
举例:
```
SELECT name, grade, LAG(grade) OVER(ORDER BY grade DESC) AS last_grade, LEAD(grade) OVER(ORDER BY grade DESC) AS next_grade FROM students;
```
5. FIRST_VALUE() / LAST_VALUE():分别返回整个窗口中第一行或最后一行的数据值。
举例:
```
SELECT name, grade, FIRST_VALUE(grade) OVER() AS lowest_grade, LAST_VALUE(grade) OVER() AS highest_grade FROM students;
```
阅读全文