这将获得预期的输出

;WITH CTE AS (    
    SELECT EffectiveDate, FundName, SecurityName, PercentageOfAssets
        , ROW_NUMBER() OVER (PARTITION BY FundName ORDER BY EffectiveDate DESC) AS RowNum
)    
SELECT *
FROM CTE
WHERE RowNum = 1

您可以在SQL Server中使用cross apply来执行此操作：

select t1.EffectiveDate, t1.FundName, t1.SecurityName, t1.PercentageOfAssets
from @tbl t1
cross apply (
    select t2.FundName, max(t2.EffectiveDate) maxEffDate
    from @tbl t2
    group by t2.FundName
) latest
where latest.FundName = t1.FundName
    and latest.maxEffDate = t1.EffectiveDate
order by t1.EffectiveDate

创建一个子查询，该子查询将获取MAXEffectiveDate，然后将JOIN传递给外部查询，以返回其余行：

SELECT b.MaxDate, a.FundName, a.SecurityName, a.PercentageOfAssets
FROM (
      SELECT FundName, MAX(EffectiveDate) as MaxDate
      FROM Funds
      GROUP BY FundName
) b
INNER JOIN Funds a
ON a.FundName = b.FundName
  AND a.EffectiveDate = b.MaxDate
ORDER BY a.FundName, a.SecurityName ASC

结果：

| MaxDate    | FundName | SecurityName | PercentageOfAssets |
|------------|----------|--------------|--------------------|
| 31/07/2022 | Fund A   | xxx          | 50%                |
| 31/07/2022 | Fund A   | yyy          | 50%                |
| 31/08/2022 | Fund B   | yyy          | 50%                |
| 31/08/2022 | Fund B   | zzz          | 50%                |

"小提琴“

注意：这将适用于MySQL和SQL Server。