/* Available in MySQL, SQL Server, PostgreSQL, Oracle */
SELECT
Name,
Population,
FIRST_VALUE(Population) over p_code AS first_population,
LAG(Population) over p_code AS prior_population,
LEAD(Population) over p_code AS next_population,
NTH_VALUE(Population, 3) over p_code AS third_population
FROM country
WHERE Continent = "South America"
WINDOW p_code AS (PARTITION BY Continent ORDER BY Population)
| Name |
Population |
first_population |
prior_population |
next_population |
third_population |
| Falkland Islands |
2000 |
2000 |
NULL |
181,000 |
NULL |
| French Guiana |
181,000 |
2000 |
2000 |
417,000 |
NULL |
| Suriname |
417,000 |
2000 |
181,000 |
861,000 |
417,000 |
| Guyana |
861,000 |
2000 |
417,000 |
3,337,000 |
417,000 |
| Uruguay |
3,337,000 |
2000 |
861,000 |
5,496,000 |
417,000 |
| Paraguay |
5,496,000 |
2000 |
3,337,000 |
8,329,000 |
417,000 |
| Bolivia |
8,329,000 |
2000 |
5,496,000 |
12,646,000 |
417,000 |
| Ecuador |
12,646,000 |
2000 |
8,329,000 |
15,211,000 |
417,000 |
| ... |
| LAG(expr) 返回排序中前一行的列值。
LEAD(expr) 返回排序中后一行的列值。
FIRST_VALUE(expr) 返回排序中第一行的列值。
LAST_VALUE(expr) 返回排序中最后一行的列值。
NTH_VALUE(expr, N) 返回排序中第 N 行的列值。 |