sql - 需要 SQL Server 查询来获取句点

Question

我有下表：每个成员每天都有一个 col1 值。我需要获取每个成员的 col1 为零的时期。

Member_ID   | col1            | Date         
ADavis      | 0               | 11-10-2013    
ADavis      | 0               | 11-11-2013   
ADavis      | 0               | 11-12-2013    
DSmith      | 1               | 11-10-2013    
DSmith      | 0               | 11-11-2013    
FRowden     | 0               | 11-10-2013     
FRowden     | 0               | 11-11-2013    
ADavis      | 1               | 11-13-2013   
ADavis      | 0               | 11-14-2013    
DSmith      | 1               | 11-12-2013    
DSmith      | 1               | 11-13-2013    
FRowden     | 1               | 11-12-2013    
FRowden     | 0               | 11-13-2013

输出

Member_ID   | Start_date      | End_date    
ADavis      | 11-10-2013      | 11-12-2013  
ADavis      | 11-14-2013      | 11-14-2013  
DSmith      | 11-11-2013      | 11-11-2013  
FRowden     | 11-10-2013      | 11-11-2013  
FRowden     | 11-13-2013      | 11-13-2013

标签： sqlsql-serversql-server-2008

Answer 1

您可以使用以下差异row_numbers()：

select Member_ID, min(date), max(date)
from (select *, row_number() over (partition by Member_ID order by date) seq1,
                row_number() over (partition by Member_ID, col1 order by date) seq2
      from table t
      ) t
where col1 = 0
group by Member_ID, (seq1 - seq2);