我手頭有這個問題，我無法找到解決的最佳方法。我們有一個學生及其學習權利的資料庫。每個學生都有唯一的 studentId，每個學生可以有 1-N 個學習權利，例如，有些可能超過 10 個。每個權利都有一個結束日期。
所以我們想要實作的是一個查詢，在以下條件下為每個學生生成一行。我正在使用 MS SQL。
- 如果學生只有一項權利，則回傳該權利
- 如果有多個，則回傳結束日期最遠的那個
- 如果多個權利具有相同的 MAX 結束日期，則回傳具有最大權利編號的權利
在下面的示例中，理想情況下它將回傳以下行：
Aaron A 的第 2 行
查理 C 的第 4 行
弗蘭克 F 的第 5 行
任何想法都非常感謝！
| 排 | 學生卡 | 學生姓名 | 權利 ID | 權利結束日期 |
|---|---|---|---|---|
| 1 | 100 | 亞倫 | 100500 | 2021-12-31 |
| 2 | 100 | 亞倫 | 200600 | 2021-12-31 |
| 3 | 200 | 查理 C | 300800 | 2021-10-31 |
| 4 | 200 | 查理 C | 400900 | 2022-01-31 |
| 5 | 300 | 弗蘭克 | 500100 | 2022-09-31 |
| 6 | 300 | 弗蘭克 | 500900 | 2021-02-31 |
uj5u.com熱心網友回復：
使用ROW_NUMBER：
WITH cte AS (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY studentId
ORDER BY entitlementEndDate DESC, entitlementId DESC) rn
FROM yourTable t
)
SELECT row, studentId, studentName, entitlementId, entitlementEndDate
FROM cte
WHERE rn = 1;
uj5u.com熱心網友回復：
首先，2022-09-31 和 2021-02-31 是很奇怪的日期……所以我將它們替換為 2022-09-30 和 2021-02-28。
也就是說，您的查詢應該是：
with cte (rowId, [row], studentId, studentName, entitlementId, entitlementEndDate)
as
(
select ROW_NUMBER() over (partition by studentId order by entitlementEndDate desc,entitlementId desc),* from MyTable
)
select [row], studentId, studentName, entitlementId, entitlementEndDate from cte where rowId = 1
這將回傳： | 行 | 學生 ID |學生姓名 | 權利 ID | 權利結束日期 | | ----------- | ----------- | -------------------------------------------------- | ------------- | ------------------ | | 2 | 100 | 亞倫 A |200600 |2021-12-31| | 4 | 200 | 查理 C |400900 |2022-01-31| | 5 |300 |弗蘭克 F |500100 | 2022-09-30|
您可以使用此代碼進行檢查（如果您直接粘貼此代碼，它將對以后的問題很有用）：
create table #MyTable (
[row] int,
studentId int,
studentName varchar(50),
entitlementId int,
entitlementEndDate date
)
insert into #MyTable values (1, 100, 'Aaron A', 100500, '2021-12-31'),
(2 ,100 ,'Aaron A', 200600, '2021-12-31'),
(3 ,200 ,'Charlie C', 300800, '2021-10-31'),
(4 ,200 ,'Charlie C', 400900, '2022-01-31'),
(5 ,300 ,'Frank F', 500100, '2022-09-30'),
(6 ,300 ,'Frank F', 500900, '2021-02-28');
with cte (rowId, [row], studentId, studentName, entitlementId, entitlementEndDate)
as
(
select ROW_NUMBER() over (partition by studentId order by entitlementEndDate desc,entitlementId desc),* from #MyTable
)
select [row], studentId, studentName, entitlementId, entitlementEndDate from cte where rowId = 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/346770.html
標籤：sql
上一篇：根據行條目轉換列資料