我試圖查詢資料，我需要挑選那些最后一個事件在條件1中是3，在條件2中是4的資料，并且最后一個事件應該是在1月份內。
資料如下,
col1 condition1 condition2 date
1234 0 1 01/01/2020
1234 1 2 02/01/2020
1234 2 3 04/01/2020
1234 3 4 10/01/2020
5678 0 1 25/01/2020
5678 1 2 26/01/2020
5678 2 3 28/01/2020
5678 3 4 03/02/2020
8901 0 1 17/01/2020
8901 1 2 18/01/2020
8901 2 3 20/01/2020>
8901 3 4 22/01/2020
8901 4 5 24/01/2020
8901 5 6 26/01/2020
3467 0 1 13/01/2020
3467 1 2 15/01/2020
3467 2 3 16/01/2020
3467 3 4 24/01/2020
3467 4 5 27/01/2020
3467 5 6 28/01/2020
預期輸出
col1 condition1 condition2 date
1234 3 4 10/01/2020
uj5u.com熱心網友回復：
select *
from mytable
資格
rank()
over（partition by col1
order by datecol desc) = 1 --最后一個事件。
-- 附加條件 -- 附加條件
and datecol between date '2021-01-01' and date '2021-01-31'
and condition1 = 3
and condition2 = 4;
uj5u.com熱心網友回復：
翻譯"......挑選最后一個有條件的條目[......] 。 其最后一個事件在條件1中是3，在條件2中是4，并且最后一個事件應該是在1月內。" 翻譯成SQL:
SELECT col1, condition1, condition2, "date"
FROM tablename AS t1
WHERE condition1 = 3 AND condition2 = 4
AND EXTRACT(MONTH) keyword">MONTH FROM "date") = 1
AND NOT EXISTS （SELECT * FROM tablename AS t2
WHERE t2.col1 = t1.col1
AND t2. "date" > t1. "date")
BTW，DATE是Teradata的保留字（https://en.wikipedia.org/wiki/SQL_reserved_words）。如果你有一個具有該名稱的列，它必須以"date"為界。
編輯："對不起，我忘了提到年份：（它應該提取2020年1月和2020年的資料。"
SELECT col1, condition1, condition2, "date"
FROM tablename AS t1
WHERE condition1 = 3 AND condition2 = 4
AND "date" BETWEEN DATE'2021-01-01' AND DATE'2021-01-31'
AND NOT EXISTS （SELECT * FROM tablename AS t2
WHERE t2.col1 = t1.col1
AND t2. "date" > t1. "date")
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/311907.html
標籤：