本文介绍了如何计算 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 Server 中列中值的最连续出现次数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!