sql - 根据条件获取每个月表中的第一行

Question

我有table很多records，columns像这样：

under_symbol|quote_date|percent|type

under_symbol is a string,
quote_date is a datetime2,
percent is a float,
type is a char that can be 0 or 1

我需要得到每个给定月份的record第一个，其中> .19 和< .21 和= 1tableunder_symbolpercentpercenttype

这将为 each 返回所有这些under_symbol，但我只需要每个under_symbol匹配它的月份的第一条记录：

SELECT * 
       [under_symbol]
      ,[quote_date]
      ,[type]
      ,[percent]
  FROM [Underlying].[dbo].[Table_EOD]
  where [percent] > .18 and [percent] < .22 and type = '1'

问题是我得到了给定月份的很多记录。对于每个月，我只需要与这些条件匹配的每个唯一符号在该月中最早的那个。

标签： sqlsql-servertsql

Answer 1

你可以试试这样的

with month_cte as (
    select *, row_number() over (partition by eomonth(quote_date) order by quote_date) rn
    from [Underlying].[dbo].[Table_EOD]
    where [percent] > .18
          and [percent] < .22 
          and type = '1')
SELECT [under_symbol]
      ,[quote_date]
      ,[type]
      ,[percent]
  FROM month_cte
  where rn=1;