如何计算 SQL Server 中列中值的最连续出现次数 [英] How to count most consecutive occurrences of a value in a Column in 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 中列中值的最连续出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！