如果您只需要一个exists()，为什么还需要group by呢？

;with data as
(
    select ROW_NUMBER() OVER (ORDER BY date) AS number, * from roster
)
select * from data where 
    not exists -- Just compare with the previous column, if match say bye
    (
        select * from data prev where 1 = 1
            and prev.first = data.first 
            and prev.second = data.second 
            and prev.third = data.third 
            and prev.fourth = data.fourth 
            and prev.number + 1 = data.number
    )

SQL小提琴

• • 编辑**

;with data as
(
    select
        ROW_NUMBER() OVER (ORDER BY date) AS number, 
        ROW_NUMBER() OVER (PARTITION BY first, second, third, fourth ORDER BY date) AS part,
        *
    from roster
)
select MIN(date) as startdate, MAX(date) as enddate, COUNT(*) count, first, second, third, fourth
from data group by first, second, third, fourth, number - part
order by number - part

SQL小提琴

您可以按第一个、第二个、第三个、第四个值进行分组，然后选择遇到这些值的第一个日期（min（date））或最后一次出现的日期（max（date））
遇到的最后日期示例：小提琴

SELECT min(date) as startdate ,max(date) as enddate,  first, second, third, fourth 
from roster
GROUP BY first, second, third, fourth

EDIT：编辑之前的质询，以包括开始和结束日期
额外：一些我在玩的时候等待你的答复：包括数值出现在1个字段中的日期列表：

SELECT first, second, third, fourth,
STUFF((
          SELECT ',' + convert(varchar(25),T.date)
          FROM roster T
          WHERE A.first = T.first
          AND A.second = T.second
          AND A.third = T.third
          AND A.fourth = T.fourth
          ORDER BY T.date
          FOR XML PATH('')), 1, 1, '') as dates

from roster A
GROUP BY first, second, third, fourth

编辑：我已经很接近你想要的了，但还不完全，但是我不知道如何让它更接近，我想这是我所能走的，剩下的就看别人了：D：SQLFIDDLE语言

SELECT b.date as startdate, a.date as enddate, a.first, a.second, a.third, a.fourth FROM
(Select ROW_NUMBER() 
        OVER (ORDER BY first, second, third, fourth,date ) AS Row,
        date,
        first,second,third,fourth
from roster) A
JOIN 
(Select ROW_NUMBER() 
        OVER (ORDER BY first, second, third, fourth,date ) AS Row,
        date,
        first,second,third,fourth
from roster) B
ON A.row = b.row + 1
WHERE a.first = b.first
and a.second = b.second
and a.third = b.third
and a.fourth = b.fourth
UNION
select max(date) as startdate, null as enddate, first, second, third, fourth
FROM roster
group by first, second, third, fourth
having count(*) = 1;

我一直在寻找自己的方法来实现这一点，并发现您也可以使用窗口函数来实现这一点：

SELECT *,
  ARRAY_AGG(STRUCT(first, second, third, fourth)) OVER (
    ORDER BY date
    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING 
  )[OFFSET(0)] prior_row
FROM
  roster r 
QUALIFY
  prior_row IS NULL
  OR prior_row <> STRUCT(first, second, third, fourth)