我需要在第一次填寫申請人 ID 的分鐘（日期）之后通過 web_user_id 找到空的申請人 ID
例如，對于 web_user_id 23，我們會在 row_id = 3 之后找到 null
對于 web_user_id 90，我們會在 row_id = 11 之后找到 null
https://prnt.sc/264ofhg
表格是：
| row_id | applicant_id | web_user_id | date |
| ------- | ------------- | ------------- | ---- |
| 1 | null | 23 | 2020 |
| 2 | null | 23 | 2021 |
| 3 | 77 | 23 | 2022 |
| 4 | 77 | 23 | 2023 |
| 5 | 77 | 23 | 2024 |
| 6 | null | 23 | 2025 |
| 7 | 77 | 23 | 2026 |
| 8 | null | 23 | 2027 |
| 9 | 77 | 23 | 2028 |
| 10 | null | 90 | 2020 |
| 11 | 55 | 90 | 2021 |
| 12 | 55 | 90 | 2022 |
| 13 | 55 | 90 | 2023 |
| 14 | 55 | 90 | 2024 |
| 15 | null | 90 | 2025 |
| 16 | 55 | 90 | 2026 |
| 17 | 55 | 90 | 2027 |
條件是： 選擇 min(date)、applicant_id、row_id、web_user_id 并且在此日期之后我需要找到申請人 ID 為空的行
結果我會得到這張桌子： https ://prnt.sc/264om6u
| row_id | applicant_id | web_user_id | date |
| ------- | ------------- | ------------- | ---- |
| 6 | null | 23 | 2025 |
| 8 | null | 23 | 2027 |
| 15 | null | 90 | 2025 |
用于創建表的 SQL
create table dbo.tabl (
row_id int,
applicant_id int,
web_user_id int,
"date" int
);
insert into dbo.tabl values
(1, null, 23, 2020),
(2, null, 23, 2021),
(3, 77, 23, 2022),
(4, 77, 23, 2023),
(5, 77, 23, 2024),
(6, null, 23, 2025),
(7, 77, 23, 2026),
(8, null, 23, 2027),
(9, 77, 23, 2028),
(10, null, 90, 2020),
(11, 55, 90, 2021),
(12, 55, 90, 2022),
(13, 55, 90, 2023),
(14, 55, 90, 2024),
(15, null, 90, 2025),
(16, 55, 90, 2026),
(17, 55, 90, 2027);
uj5u.com熱心網友回復：
這是我嘗試過的，也是一個簡單的查詢。你也可以試試這個
Select applicant_id, row_id, web_user_id,min("date")
from tabl
where applicant_id is null
and "date" > 2024
group by applicant_id, row_id, web_user_id,"date";
uj5u.com熱心網友回復：
**This request was written in a rush. But I think it will help you.**
Select *
from (Select *
from (Select t.*,
LAG(applicant_id) OVER(PARTITION BY WEB_USER_ID ORDER BY
t."date") LAG_,
ROW_NUMBER() OVER(PARTITION BY t.WEB_USER_ID Order BY
t."date") RN
from tabl_test t)
where not (applicant_id is null and lag_ is null))
where applicant_id is null
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/411014.html
標籤：