我只需要提取一個帳戶的最大行號。我知道這是一個分組問題。
當前資料：
| ACCOUNT_UID | ID | 名稱 | ACADEMIC_PERIOD | CAT_BY_DATE | CAT_DATE | MAX_ROW |
|---|---|---|---|---|---|---|
| 美國廣播公司 | 美國廣播公司 | 大力水手 | 202190 | 彩管 | 2021 年 9 月 15 日 | 1 |
| 美國廣播公司 | 美國廣播公司 | 大力水手 | 202190 | 長沙 | 2021 年 10 月 4 日 | 2 |
我只需要第二行。
當前查詢：
Select
A.ACCOUNT_UID,
A.ID,
A.NAME,
A.ACADEMIC_PERIOD,
A.cat_by_date,
A.Cat_date,
Max (A.row_num) max_row
From
(select RAD.ACCOUNT_UID,
RAD.ID,
RAD.NAME,
RAD.ACADEMIC_PERIOD,
listagg(RAD.CATEGORY, ', ') within group (order by RAD.CATEGORY) as cat_by_date,
trunc(RAD.TRANSACTION_DATE) as Cat_date,
ROW_NUMBER() OVER (partition by RAD.ACCOUNT_UID ORDER BY trunc(RAD.TRANSACTION_DATE)) as
row_num
from RAD
where RAD.ACADEMIC_PERIOD ='202190'
and RAD.CATEGORY in ('CPT', 'CSH')
group by
RAD.ACCOUNT_UID,
RAD.ID,
RAD.NAME,
RAD.ACADEMIC_PERIOD,
trunc(RAD.TRANSACTION_DATE)
order by 1 ) A
group by
A.ACCOUNT_UID,
A.ID,
A.NAME,
A.ACADEMIC_PERIOD
A.cat_by_date,
A.Cat_date
Order by 1
uj5u.com熱心網友回復：
您可以嘗試以下方法：
Select
A.ACCOUNT_UID,
A.ID,
A.NAME,
A.ACADEMIC_PERIOD,
A.cat_by_date,
A.Cat_date
From
(select RAD.ACCOUNT_UID,
RAD.ID,
RAD.NAME,
RAD.ACADEMIC_PERIOD,
listagg(RAD.CATEGORY, ', ') within group (order by RAD.CATEGORY) as cat_by_date,
trunc(RAD.TRANSACTION_DATE) as Cat_date,
ROW_NUMBER() OVER (partition by RAD.ACCOUNT_UID ORDER BY trunc(RAD.TRANSACTION_DATE) DESC) as rn
from RAD
where RAD.ACADEMIC_PERIOD ='202190'
and RAD.CATEGORY in ('CPT', 'CSH')
group by
RAD.ACCOUNT_UID,
RAD.ID,
RAD.NAME,
RAD.ACADEMIC_PERIOD,
trunc(RAD.TRANSACTION_DATE)
order by 1 ) A
Where A.rn = 1
Order by 1
這個想法是row_number按照transaction_dateper every 的降序應用視窗函式account_uid并選擇獲得的第一行。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/409095.html
標籤：
上一篇：OracleSQLFor回圈選項
下一篇：根據多個條件洗掉組