本文介绍了如何计算 SQL Server 中列中值的最连续出现次数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的数据库中有一个表 Attendance.
I have a table Attendance in my database.
Date | Present
------------------------
20/11/2013 | Y
21/11/2013 | Y
22/11/2013 | N
23/11/2013 | Y
24/11/2013 | Y
25/11/2013 | Y
26/11/2013 | Y
27/11/2013 | N
28/11/2013 | Y
我想计算一个值 Y 或 N 最连续出现的次数.
I want to count the most consecutive occurrence of a value Y or N.
例如在上表中Y出现2, 4 &1次.所以我想要 4 作为我的结果.如何在 SQL Server 中实现这一点?
For example in the above table Y occurs 2, 4 & 1 times. So I want 4 as my result.
How to achieve this in SQL Server?
任何帮助将不胜感激.
推荐答案
试试这个:-
连续日期之间的差异将保持不变
The difference between the consecutive date will remain constant
Select max(Sequence)
from
(
select present ,count(*) as Sequence,
min(date) as MinDt, max(date) as MaxDt
from (
select t.Present,t.Date,
dateadd(day,
-(row_number() over (partition by present order by date))
,date
) as grp
from Table1 t
) t
group by present, grp
)a
where Present ='Y'
SQL FIDDLE
这篇关于如何计算 SQL Server 中列中值的最连续出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！