對于 SQL Server，我有一個名為 hcp_funding_packages 的表，其中包含
client_sysid (int)
package_level (int)
created_at (datetime)
對于每個 client_sysid，我都需要最新 package_level 更改的條目。
因此，例如，如果我有以下內容
| client_sysid | 包級別 | 日期 |
|---|---|---|
| 1 | 1 | 2021-01-01 |
| 1 | 3 | 2021-01-02 |
| 1 | 3 | 2021-01-03 |
| 1 | 1 | 2021-01-04 |
| 1 | 1 | 2021-01-05 |
| 1 | 1 | 2021-01-06 |
然后我需要第 4 行的粗體
uj5u.com熱心網友回復：
您可以為此使用LEAD和ROW_NUMBER視窗函式的組合
WITH Changed AS (
SELECT *,
CASE WHEN package_level <> LEAD(package_level, 1, -999) OVER (PARTITION BY client_sysid ORDER BY date DESC)
THEN 1 END AS IsDifferent
FROM hcp_funding_packages fp
),
Numbered AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY client_sysid ORDER BY date DESC) AS rn
FROM Changed fp
WHERE IsDifferent = 1
)
SELECT
client_sysid,
package_level,
date
FROM Numbered fp
WHERE rn = 1;
資料庫<>小提琴
(client_sysid, date DESC)如果您希望它具有高性能，請確保您有一個索引。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/343701.html
標籤：sql sql-server 查询语句