使用
ROW_NUMBER()window函数获取具有
Run_amt最大月份的的行，然后进行条件聚合：
SELECt t.ID, t.Name, t.Amount, MAX(CASE WHEN LAST_DAY(Date) = LAST_DAY(CURRENT_DATE) THEN cal_amt END) cal_amt, MAX(CASE WHEN t.rn = 1 THEN Run_amt END) Run_amtFROM ( SELECt *, ROW_NUMBER() OVER (PARTITION BY ID, Name, Amount ORDER BY Date DESC) rn FROM tablename) tGROUP BY t.ID, t.Name, t.Amount
或者：
SELECt t.ID, t.Name, t.Amount, MAX(t.cal_amt) cal_amt, MAX(t.Run_amt) Run_amtFROM ( SELECt ID, Name, Amount, MAX(CASE WHEN LAST_DAY(Date) = LAST_DAY(CURRENT_DATE) THEN cal_amt END) OVER (PARTITION BY ID, Name, Amount ORDER BY Date DESC) cal_amt, FIRST_VALUE(Run_amt) OVER (PARTITION BY ID, Name, Amount ORDER BY Date DESC) Run_amt FROM tablename) t GROUP BY t.ID, t.Name, t.Amount
参见演示。
结果：
> ID | Name | Amount | cal_amt | Run_amt> -: | :----- | -----: | ------: | ------:> 1 | Test | 15000 | 12000 | 15000> 2 | Test_1 | 18000 | 16000 | 25000
