如何获得连续日期的计数 [英] How to get count of consecutive dates

问题描述

例如，有一些带有日期的表：

For example there is some table with dates:

2015-01-01
2015-01-02
2015-01-03
2015-01-06
2015-01-07
2015-01-11

我必须编写ms sql查询，该查询将从表中的每个日期返回连续日期的计数。因此结果将如下所示：

I have to write ms sql query, which will return count of consecutive dates starting from every date in the table. So the result will be like:

2015-01-01   1
2015-01-02   2
2015-01-03   3
2015-01-06   1
2015-01-07   2
2015-01-11   1

在我看来，我应该使用LAG和LEAD函数，但是现在我什至无法想象思维方式。

It seems to me that I should use LAG and LEAD functions, but now I even can not imagine the way of thinking.

推荐答案

CREATE TABLE #T ( MyDate DATE) ;
INSERT #T VALUES ('2015-01-01'),('2015-01-02'),('2015-01-03'),('2015-01-06'),('2015-01-07'),('2015-01-11')

SELECT 
    RW=ROW_NUMBER() OVER( PARTITION BY GRP  ORDER BY MyDate) ,MyDate
FROM
(
SELECT 
    MyDate, DATEDIFF(Day, '1900-01-01' , MyDate)- ROW_NUMBER() OVER( ORDER BY MyDate ) AS GRP
FROM #T 
) A

DROP TABLE #T;

这篇关于如何获得连续日期的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！