如何基于MYSQL中的日期获取记录 [英] How to Fetch records based on the Dates in MYSQL

问题描述

我有示例数据

 ID  Name  Amount  cal_amt Run_amt  Dates
 1   Test   15000    0.00  15000    2020-06-01
 1   Test   15000    0.00  30000    2020-04-01
 1   Test   15000    12000 30000    2020-05-01
 2   Test_1   18000   0.00  25000    2020-06-01
 2   Test_1   18000   0.00  35000    2020-04-01
 2   Test_1   18000   16000 35000    2020-05-01

我需要获得Run_Amount的最大值(月)，即2020-06-01-> 15000

I need to get MAX(month) of Run_Amount i.e : 2020-06-01 --> 15000

需要获取本月的cal_amt，即:2020-05-01-> 12000和0.00也与本月2020-04-01相关

Need to fetch cal_amt of current month i.e : 2020-05-01 --> 12000 and 0.00 also relates to this month 2020-04-01

我需要这样的输出:

 ID  Name  Amount  cal_amt Run_amt  
 1   Test   15000    12000  15000 
 2   Test_1 18000    16000  25000

这是示例数据，但我尝试使用 MAX()条件和

It is sample data but have another few more columns are there I have tried with MAX() conditions and

ROW_NUMBER()over (PARTITION BY run_amt order by Date )

有人可以建议我最好的方法

Can anyone suggest me the best way

推荐答案

使用ROW_NUMBER()窗口函数获取具有最大月份的Run_amt的行，然后进行条件聚合:

Use ROW_NUMBER() window function to get the row with the Run_amt of the max month and then with conditional aggregation:

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_amt
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY ID, Name, Amount ORDER BY Date DESC) rn
  FROM tablename
) t
GROUP 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_amt
FROM (
  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

请参见演示.
结果:

See the demo.
Results:

> ID | Name   | Amount | cal_amt | Run_amt
> -: | :----- | -----: | ------: | ------:
>  1 | Test   |  15000 |   12000 |   15000
>  2 | Test_1 |  18000 |   16000 |   25000

这篇关于如何基于MYSQL中的日期获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！