如何使用 Min() 和 Where() SQL 函数获取最早的记录

Question

我正在尝试提取最早日期的记录，但一直遇到错误。我以为我可以这样做作为 WHERE 子句的一部分，但我错了。如何将数据拉到满足当前代码中两个条件的位置？

left outer join 
(SELECT
[RID]
,[p_rid]
,[Budget Records.Budget ID]
,[Budget Records.Budget approval process]
,[Budget Records.Inflation base date]
,[Budget Records.Budget Stage]
,CAST(ISNULL([Budget Records.Total capital budget],0) as float) as [CAR Total capital budget]
FROM [FSG_DATA_TEAM].[dbo].[BR]
WHERE [Budget Records.Budget Stage] in ('Cost Plan 1') and min([Budget Records.Inflation base date])) as y

我得到输出"在预期条件的上下文中指定的非布尔类型的表达式，靠近'("，但我希望的是根据最早的通货膨胀基准日期返回值。

Answer 1

如果您的数据库支持使用窗口分析函数(似乎SQL-Server(，您可以将查询的上述部分转换为：

left outer join 
(
    SELECT *
    (
     SELECT
          [RID]
          ,[p_rid]
          ,[Budget Records.Budget ID]
          ,[Budget Records.Budget approval process]
          ,[Budget Records.Inflation base date]
          ,row_number() over (order by [Budget Records.Inflation base date]) as rn
          ,[Budget Records.Budget Stage]
          ,CAST(ISNULL([Budget Records.Total capital budget],0) as float) as [CAR Total capital budget]
      FROM [FSG_DATA_TEAM].[dbo].[BR]
     WHERE [Budget Records.Budget Stage] in ('Cost Plan 1') 
    ) 
    WHERE rn = 1
) as y   

以限制由于列[Budget Records.Inflation base date]最小值而导致的结果。