根据前一行日期，每年最多一行日期

Question

我有一个有两列的表，我想用规则获取6年的数据

1. 第一行将是在输入日期之前可用的最大日期行(我将传递一个输入日期(

2. 从第二行到第六行，我需要比前一行数据更早的最大值(日期行(，同一年不应该有2行，我只需要根据前一行的最新一行，但不需要在同一年。

   declare @tbl table (id int identity, marketdate date )
   insert into @tbl (marketdate)
   values('2018-05-31'),
   ('2017-06-01'),
   ('2017-05-28'),
   ('2017-04-28'),
   ('2016-05-26'),
   ('2015-04-18'),
   ('2015-04-20'),
   ('2015-03-18'),
   ('2014-05-31'),
   ('2014-04-18'),
   ('2013-04-15')
   

输出：

id  marketdate
1    2018.05.31
3    2017.05.28
5    2016.05.27
7    2015.04.20
9    2014.04.18
10   2013.04.15

Answer 1

你不能用/desc的简单订单来完成吗？

SELECT TOP 6 id, max(marketdate) FROM tbl
WHERE tbl.marketdate <= @date
GROUP BY YEAR(marketdate), id, marketdate
ORDER BY YEAR(marketdate) DESC

Answer 2

纯粹基于您的"输出"给定的样本数据，我相信以下是您想要的(每个不同年份数据的最大日期(：

SELECT TOP 6   
max(marketdate),
Year(marketDate) as marketyear
FROM @tbl
WHERE @tbl.marketdate <= getdate()
GROUP BY YEAR(marketdate)
ORDER BY YEAR(marketdate) DESC;

这个匹配你的输出的SQLFiddle

Answer 3

如果使用sql server ，则可以使用row_number

select      top 6
id
, t.marketdate
from        (   select  rn = row_number() over (partition by year(marketdate)order by marketdate desc)
, id
, marketdate
from    @tbl) as t
where       t.rn = 1
order by    t.marketdate desc

Answer 4

以下递归搜索下一个日期，该日期必须至少比上一个日期早一年。

您的参数化起始位置是我选择2018-06-01的位置。

WITH
recursiveSearch AS
(
SELECT
id,
marketDate
FROM
(
SELECT
yourTable.id,
yourTable.marketDate,
ROW_NUMBER() OVER (ORDER BY yourTable.marketDate DESC)  AS relative_position
FROM
yourTable
WHERE
yourTable.marketDate <= '2018-06-01'
)
search
WHERE
relative_position = 1
UNION ALL
SELECT
id,
marketDate
FROM
(
SELECT
yourTable.id,
yourTable.marketDate,
ROW_NUMBER() OVER (ORDER BY yourTable.marketDate DESC)  AS relative_position
FROM
yourTable
INNER JOIN
recursiveSearch
ON  yourTable.marketDate < DATEADD(YEAR, -1, recursiveSearch.marketDate)
)
search
WHERE
relative_position = 1
)
SELECT
*
FROM
recursiveSearch
WHERE 
id IS NOT NULL
ORDER BY
recursiveSearch.marketDate DESC
OPTION
(MAXRECURSION 0)

http://sqlfiddle.com/#！18/56246/13