FIRST_VALUE(scalar_expression) OVER ([PARTITION BY ...] ORDER BY ... [ROWS/RANGE ...])
LAST_VALUE(scalar_expression) OVER ([PARTITION BY ...] ORDER BY ... [ROWS/RANGE ...])
scalar_expression:標量表達式(列、常量、標量函數)。RANGE UNBOUNDED PRECEDING AND CURRENT ROW —— 這導致 LAST_VALUE 在無显式框架時返回當前行而非分區最後一行,是常見陷阱。LAST_VALUE(score) OVER (
PARTITION BY name
ORDER BY score
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
或使用 LAST_VALUE(score) OVER (PARTITION BY name ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)。
WITH test AS (
SELECT '樂可樂可的部落格' AS name, 10 AS score UNION ALL
SELECT '樂可樂可的部落格', 15 UNION ALL
SELECT '樂可樂可的部落格', 20 UNION ALL
SELECT '微軟認證', 30 UNION ALL
SELECT '微軟認證', 40 UNION ALL
SELECT '微軟認證', 40
)
SELECT
name, score,
FIRST_VALUE(score) OVER (PARTITION BY name ORDER BY score) AS first_score,
LAST_VALUE(score) OVER (
PARTITION BY name ORDER BY score
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_score
FROM test
| name | score | first_score | last_score |
|---|---|---|---|
| 樂可樂可的部落格 | 10 | 10 | 20 |
| 樂可樂可的部落格 | 15 | 10 | 20 |
| 樂可樂可的部落格 | 20 | 10 | 20 |
| 微軟認證 | 30 | 30 | 40 |
| 微軟認證 | 40 | 30 | 40 |
| 微軟認證 | 40 | 30 | 40 |
ORDER BY 排序後的第一行值,全組相同。ROWS BETWEEN ...,LAST_VALUE 會隨當前行變化(返回「截至當前行的最後值」),通常不是想要的語義。IGNORE NULLS(SQL Server 2022+ 支持)跳過 NULL 取首末非空值。