SQL Server选择每个ID的最大日期

I am trying to select max date record for each service_user_id for each finance_charge_id and the amount that is linked the highest date

我正在尝试为每个service_user_id为每个finance_charge_id选择最大日期记录，以及链接最高日期的金额

select distinct 
    s.Finance_Charge_ID, MAX(s.start_date), s.Amount  
from
    Service_User_Finance_Charges s
where 
    s.Service_User_ID = '156'
group by 
    s.Finance_Charge_ID, s.Amount

The issue is that I receive multiple entries where the amount is different. I only want to receive the amount on the latest date for each finance_charge_id

问题是我收到了多个条目，其中的数量不同。我只想收到每个finance_charge_id的最近日期的金额

At the moment I receive the below which is incorrect (the third line should not appear as the 1st line has a higher date)

此时我收到以下错误信息(第三行不应该出现，因为第一行的日期比较长)

Finance_Charge_ID   (No column name)    Amount
2                      2014-10-19       1.00
3                      2014-10-16       500.00
2                      2014-10-01       1000.00

2 个解决方案

#1

Remove the Amount column from the group by to get the correct rows. You can then join that query onto the table again to get all the data you need. Here is an example using a CTE to get the max dates:

从组中删除Amount列，以获得正确的行。然后，您可以再次将该查询加入到表中，以获得所需的所有数据。这里有一个使用CTE获取最大日期的例子:

WITH MaxDates_CTE (Finance_Charge_ID, MaxDate) AS
(
    select  s.Finance_Charge_ID, 
            MAX(s.start_date) MaxDate
    from Service_User_Finance_Charges s
    where  s.Service_User_ID = '156'
    group by s.Finance_Charge_ID
)

SELECT *
FROM Service_User_Finance_Charges
JOIN MaxDates_CTE 
    ON MaxDates_CTE.Finance_Charge_ID = Service_User_Finance_Charges.Finance_Charge_ID
    AND MaxDates_CTE.MaxDate = Service_User_Finance_Charges.start_date

#2

This can be done using a window function which removes the need for a self join on the grouped data:

这可以通过一个窗口函数来实现，该函数消除了对分组数据进行自连接的需要:

select Finance_Charge_ID,
       start_date,
       amount
from (
  select s.Finance_Charge_ID, 
         s.start_date,
         max(s.start_date) over (partition by s.Finance_Charge_ID) as max_date,
         s.Amount  
  from Service_User_Finance_Charges s
  where s.Service_User_ID = 156
) t
where start_date = max_date;

As the window function does not require you to use group by you can add any additional column you need in the output.

由于窗口函数不需要使用group by，所以可以在输出中添加任何需要的附加列。

#1