连续时间分组的问题

Question

数据如下：
vcNO dDate
A    2010-01-01
A    2010-01-02
A    2010-03-04
B    2010-03-05
B    2010-03-06
B    2010-03-07
...(连续的B，从2010-03-08到2010-04-06)
B    2010-04-07希望得到的结果：
vcNO dMin         dMax
A    2010-01-01   2010-01-03
A    2010-03-04   2010-03-05
B    2010-03-05   2010-04-05   （连续的时间超过30天的，按30天作分隔）
B    2010-04-05   2010-04-08
大家有什么好的想法没？还望不吝赐教..

Answer 1

以30为间隔的：
SET NOCOUNT ON 
declare @a table(vcNO VARCHAR(20), dDate VARCHAR(10),x INT,y int)
INSERT @a select 'A','2010-01-01',NULL,NULL 
union all select 'A','2010-01-02',NULL,NULL
union all select 'A','2010-03-04',NULL,NULL
union all select 'B','2010-03-05',NULL,NULL
union all select 'B','2010-03-06',NULL,NULL
union all select 'B','2010-03-07',NULL,NULL
union all select 'B','2010-03-08',NULL,NULL
union all select 'B','2010-03-09',NULL,NULL
union all select 'B','2010-03-10',NULL,NULL
union all select 'B','2010-03-11',NULL,NULL
union all select 'B','2010-03-12',NULL,NULL
union all select 'B','2010-03-13',NULL,NULL
union all select 'B','2010-03-14',NULL,NULL
union all select 'B','2010-03-15',NULL,NULL
union all select 'B','2010-03-16',NULL,NULL
union all select 'B','2010-03-17',NULL,NULL
union all select 'B','2010-03-18',NULL,NULL
union all select 'B','2010-03-19',NULL,NULL
union all select 'B','2010-03-20',NULL,NULL
union all select 'B','2010-03-21',NULL,NULL
union all select 'B','2010-03-22',NULL,NULL
union all select 'B','2010-03-23',NULL,NULL
union all select 'B','2010-03-24',NULL,NULL
union all select 'B','2010-03-25',NULL,NULL
union all select 'B','2010-03-26',NULL,NULL
union all select 'B','2010-03-27',NULL,NULL
union all select 'B','2010-03-28',NULL,NULL
union all select 'B','2010-03-29',NULL,NULL
union all select 'B','2010-03-30',NULL,NULL
union all select 'B','2010-03-31',NULL,NULL
union all select 'B','2010-04-01',NULL,NULL
union all select 'B','2010-04-02',NULL,NULL
union all select 'B','2010-04-03',NULL,NULL
union all select 'B','2010-04-04',NULL,NULL
union all select 'B','2010-04-05',NULL,NULL
union all select 'B','2010-04-06',NULL,NULL
union all select 'B','2010-04-07',NULL,NULLDECLARE @vc VARCHAR(20),@d VARCHAR(20),@i INT ,@m INT 
SELECT @i=0,@m=0
UPDATE @a SET 
@i=CASE WHEN @vc=vcNo AND DATEDIFF(DAY,@d,ddate)=1 THEN @i+1 ELSE 1 END,
@m=CASE WHEN @vc=vcNo AND DATEDIFF(DAY,@d,ddate)=1 THEN @m ELSE @m+1 END,
@vc=vcNo,@d=dDate,x=@i,y=@m
SELECT vcNo,MIN(dDate) dMin,convert(varchar(10),dateadd(day,1,MAX(dDate)),120) dMax FROM @a
GROUP BY vcNo,y,(x-1) /30/*
vcNo                 dMin       dMax       
-------------------- ---------- ---------- 
A                    2010-01-01 2010-01-03
A                    2010-03-04 2010-03-05
B                    2010-03-05 2010-04-04
B                    2010-04-04 2010-04-08
*/

Answer 2

假如不考虑30天那个
参考
http://blog.csdn.net/xys_777/archive/2010/06/21/5683641.aspx

Answer 3

if object_id('tb') is not null drop table tb
go
create table tb (vcNO varchar(1),dDate datetime);
insert into tb
select 'A',' 2010-01-01' union all
select 'A',' 2010-01-02' union all
select 'A',' 2010-03-04' union all
select 'B',' 2010-03-05' union all
select 'B',' 2010-03-06' union all
select 'B',' 2010-03-07' union all
select 'B',' 2010-03-08' union all
select 'B',' 2010-03-09' union all
select 'B',' 2010-03-10' union all
select 'B',' 2010-03-11' union all
select 'B',' 2010-03-12' union all
select 'B',' 2010-03-13' union all
select 'B',' 2010-03-14' union all
select 'B',' 2010-03-15' union all
select 'B',' 2010-03-16' union all
select 'B',' 2010-03-17' union all
select 'B',' 2010-03-18' union all
select 'B',' 2010-03-19' union all
select 'B',' 2010-03-20' union all
select 'B',' 2010-03-21' union all
select 'B',' 2010-03-22' union all
select 'B',' 2010-03-23' union all
select 'B',' 2010-03-24' union all
select 'B',' 2010-03-25' union all
select 'B',' 2010-03-26' union all
select 'B',' 2010-03-27' union all
select 'B',' 2010-03-28' union all
select 'B',' 2010-03-29' union all
select 'B',' 2010-03-30' union all
select 'B',' 2010-03-31' union all
select 'B',' 2010-04-01' union all
select 'B',' 2010-04-02' union all
select 'B',' 2010-04-03' union all
select 'B',' 2010-04-04' union all
select 'B',' 2010-04-05' union all
select 'B',' 2010-04-06' union all
select 'B',' 2010-04-07' union all
select 'B',' 2010-04-08' union all
select 'B',' 2010-04-09' union all
select 'B',' 2010-04-10' union all
select 'B',' 2010-04-11'
go
--sql2000 普通查询
select max(t.vcNO) as vcNO, min(t.dDate) minDate,max(t.dDate) maxDate,COUNT(1) CNT
from (select vcNO,dDate,rownum=(select count(1) 
from tb 
where vcNO=a.vcNO and dDate<a.dDate)  
      from tb a) t
group by  t.vcNO, dateadd(day,-rownum,dDate)
/*
vcNO minDate                                                maxDate                                                CNT         
---- ------------------------------------------------------ ------------------------------------------------------ ----------- 
A    2010-01-01 00:00:00.000                                2010-01-02 00:00:00.000                                2
A    2010-03-04 00:00:00.000                                2010-03-04 00:00:00.000                                1
B    2010-03-05 00:00:00.000                                2010-04-11 00:00:00.000                                38（所影响的行数为 3 行）*/--sql2000 有30天分组的查询
select vcNo ,
dateadd(day,((count(1)-1)/30 )*30,min(t.dDate)) minDate,
(case when dateadd(day,((count(1)-1)/30 )*30+29,min(t.dDate))>max(t.dDate)  then max(t.dDate) 
else dateadd(day,((count(1)-1)/30 )*30+29,min(t.dDate)) end )+1 maxDate
from (select vcNO,dDate,rownum=(select count(1) 
from tb 
where vcNO=a.vcNO and dDate<a.dDate)  
      from tb a) t
group by  t.vcNO, dateadd(day,-rownum,dDate),(rownum)/30
/*
vcNo minDate                                                maxDate                                                
---- ------------------------------------------------------ ------------------------------------------------------ 
A    2010-01-01 00:00:00.000                                2010-01-03 00:00:00.000
A    2010-03-04 00:00:00.000                                2010-03-05 00:00:00.000
B    2010-03-05 00:00:00.000                                2010-04-04 00:00:00.000
B    2010-04-04 00:00:00.000                                2010-04-12 00:00:00.000（所影响的行数为 4 行）*/

Answer 4

-- 今天比较忙：(大概模型如下)(未测试)：
--------------------------------------------------------------------------------------
CREATE TABLE date_tb(
vcNo varchar(10),
dDate datetime);INSERT INTO date_tb(vcNo, dDate) values('A', '2010-01-01');
INSERT INTO date_tb(vcNo, dDate) values('A', '2010-01-02');
INSERT INTO date_tb(vcNo, dDate) values('A', '2010-01-04');-- 循环插入B的数据
DECLARE
  @v_fromDate datetime,
  @v_toDate datetime;
begin
  set @v_fromDate = '2010-03-05';
  set @v_toDate = '2010-04-07';
  while @v_fromDate <= @v_toDate
  begin
    INSERT INTO date_tb(vcNo, dDate) values('B', @v_fromDate);
    set @v_fromDate = @v_fromDate + 1;
  end
end-- CREATE PROCED
select * from date_tb;
------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[date_tb_proc]
AS
  DECLARE @vcNo varchar(10);
  DECLARE @dDate datetime;  DECLARE @old_vcNo varchar(10);  -- 上一条记录的 vcNo 值
  DECLARE @old_dDate datetime;    -- 上一条记录的 dDate 值  DECLARE @new_vcNo varchar(10);  -- 上一条记录的 vcNo 值
  DECLARE @new_dDate datetime;  DECLARE @v_count INT;  -- 记数器(用于判断连续时间是否超过30天)
  DECLARE @cnt INT;      -- 保存切换 vcNO时，不插入重复记录
  
BEGIN
  CREATE TABLE #date_tmp(
  vcNo varchar(10),
  dDate datetime);  SET @v_count = 1;  SELECT top 1 @old_vcNo = vcNo, @old_dDate = dDate Order by vcNo,dDate;  Declare @MyData Cursor 
  Set @MyData = Cursor FOR 
    Select vcNO,dDate from [date_tb] Order by vcNo,dDate;
  Open @MyData
  Fetch next from  @MyData Into @vcNO, @dDate;
    While @@FETCH_STATUS = 0 
    Begin
      IF @old_vcNo = @vcNo -- 如果上条记录的 vcNo 等于 本条记录的 vcNo
      BEGIN
        IF datediff(@old_dDate,@dDate) =1 AND @v_count <= 30 
        -- 如果：上条记录的日期 与本条记录的日期 相隔一天，并且连续时间没有超过 30 天
          SET @old_dDate = @dDate;
        ELSE
        -- 否则：
        BEGIN
          INSERT INTO date_tb(vcNo, dDate) values(@vcNo, @old_dDate+1); -- 先插入
          SET @old_dDate = @dDate; -- 再重置 @old_dDate
  SET @v_count = 0; -- Wile 外面再加 1
        END
      END
      ELSE -- 否则( 如果上条记录的 vcNo 不等于 本条记录的 vcNo )
      BEGIN
          SELECT @cnt= COUNT(1)  FROM date_tb WHERE vcNo = @old_vcNO AND dDate = @old_dDate;
          IF @cnt = 0
            INSERT INTO date_tb(vcNo, dDate) VALUES(@old_vcNO, @old_dDate+1);
      END
  Fetch next from  @MyData Into @SizeCode,@SizeName
  End 
  Close @MyData 
  Deallocate @MyData 
END

Answer 5

------------------------------- 搭建测试环境：-------------------------------------------------------
CREATE TABLE date_tb(
vcNo varchar(10),
dDate datetime);INSERT INTO date_tb(vcNo, dDate) values('A', '2010-01-01');
INSERT INTO date_tb(vcNo, dDate) values('A', '2010-01-02');
INSERT INTO date_tb(vcNo, dDate) values('A', '2010-03-04');-- 循环插入B的数据
DECLARE
  @v_fromDate datetime,
  @v_toDate datetime;
begin
  set @v_fromDate = '2010-03-05';
  set @v_toDate = '2010-04-07';
  while @v_fromDate <= @v_toDate
  begin
    INSERT INTO date_tb(vcNo, dDate) values('B', @v_fromDate);
    set @v_fromDate = @v_fromDate + 1;
  end
end-- CREATE PROCED
select * from date_tb;---------------------------------- 创建存储过程：--------------------------------------------------------
ALTER PROCEDURE [dbo].[date_tb_proc]
AS
  DECLARE @vcNo varchar(10);
  DECLARE @dDate datetime;  DECLARE @old_vcNo varchar(10);  -- 上一条记录的 vcNo 值
  DECLARE @old_dDate datetime;    -- 上一条记录的 dDate 值  DECLARE @new_vcNo varchar(10);  -- 上一条记录的 vcNo 值
  DECLARE @new_dDate datetime;  DECLARE @v_count INT;           -- 记数器(用于判断连续时间是否超过30天)
  DECLARE @cnt INT;               -- 保存切换 vcNO时，不插入重复记录
  
BEGIN
  CREATE TABLE #date_tmp(
  vcNo varchar(10),
  from_dDate datetime,
  to_dDate datetime);  SET @v_count = 0;  -- 初始化 @old_vcNo，@old_dDate，@new_dDate 变量值
  SELECT top 1 @old_vcNo = vcNo,
               @old_dDate = dDate, @new_dDate = dDate  FROM [date_tb] 
    Order by vcNo,dDate;  Declare @MyData Cursor 
  Set @MyData = Cursor FOR 
    Select vcNO,dDate from [date_tb] Order by vcNo,dDate;
  Open @MyData
  Fetch next from  @MyData Into @vcNO, @dDate;
  While @@FETCH_STATUS = 0
  BEGIN
    IF @old_vcNo = @vcNo -- 如果上条记录的 vcNo 等于 本条记录的 vcNo
    BEGIN
      IF (datediff(day,@new_dDate,@dDate) > 1 OR @v_count > 30)
      BEGIN 
        -- 如果：上条记录的日期 与本条记录的日期 相隔超过一天，或者连续时间超过 30 天
        INSERT INTO #date_tmp(vcNo, from_dDate, to_dDate) VALUES(@old_vcNO, @old_dDate, @new_dDate+1);
        SET @old_dDate = @dDate;
        SET @new_dDate = @dDate;
SET @v_count = 1;        -- 递增
      END
      ELSE
      -- 否则：
      BEGIN
        SET @new_dDate = @dDate;      -- 重置 @new_dDate
SET @v_count = @v_count + 1;  -- 重置 @v_count 
      END
    END    IF @old_vcNo <> @vcNo -- 否则( 如果上条记录的 vcNo 不等于 本条记录的 vcNo )
    BEGIN
      SELECT @cnt= ISNULL(COUNT(1),0)  FROM #date_tmp 
       WHERE vcNo = @old_vcNO 
     AND from_dDate = @old_dDate
     AND to_dDate = @new_dDate + 1; -- 先判断是否已经插入本条记录，若没有，将其插入
      IF @cnt = 0
      BEGIN
        INSERT INTO #date_tmp(vcNo, from_dDate, to_dDate) VALUES(@old_vcNO, @old_dDate, @new_dDate+1);
      END
      SET @old_vcNo = @vcNo;   -- 重置 @old_vcNo
      SET @old_dDate = @dDate; -- 重置 @old_dDate
      SET @new_dDate = @dDate; -- 重置 @old_dDate
      SET @v_count = 1;        -- 重置 @v_count 
    END
    Fetch next from  @MyData Into @vcNO, @dDate;
  End 
  Close @MyData 
  Deallocate @MyData  -- 先判断是否已经插入本条记录，若没有，将其插入
  SELECT @cnt= ISNULL(COUNT(1),0)  FROM #date_tmp 
   WHERE vcNo = @old_vcNO 
     AND from_dDate = @old_dDate
     AND to_dDate = @new_dDate + 1; 
  IF @cnt = 0
  BEGIN
   INSERT INTO #date_tmp(vcNo, from_dDate, to_dDate) VALUES(@old_vcNO, @old_dDate, @new_dDate+1);
  END  SELECT vcNo, from_dDate, to_dDate FROM #date_tmp;
  DROP TABLE #date_tmp;
END
--------------------------------------- 执行测试：------------------------------------EXEC date_tb_proc;

Answer 6

-- 呵呵：拿分来................ CSDN 里面没有人能解决楼主的问题，被我给解决啦...............-- 呵呵呵呵........................

Answer 7

SET NOCOUNT ON 
declare @a table(vcNO VARCHAR(20), dDate VARCHAR(10))
INSERT @a select 'A','2010-01-01'
union all select 'A','2010-01-02'
union all select 'A','2010-03-04'
union all select 'B','2010-03-05'
union all select 'B','2010-03-06'
union all select 'B','2010-03-07'
union all select 'B','2010-03-08'
union all select 'B','2010-03-09'
union all select 'B','2010-03-10'
union all select 'B','2010-03-11'
union all select 'B','2010-03-12'
union all select 'B','2010-03-13'
union all select 'B','2010-03-14'
union all select 'B','2010-03-15'
union all select 'B','2010-03-16'
union all select 'B','2010-03-17'
union all select 'B','2010-03-18'
union all select 'B','2010-03-19'
union all select 'B','2010-03-20'
union all select 'B','2010-03-21'
union all select 'B','2010-03-22'
union all select 'B','2010-03-23'
union all select 'B','2010-03-24'
union all select 'B','2010-03-25'
union all select 'B','2010-03-26'
union all select 'B','2010-03-27'
union all select 'B','2010-03-28'
union all select 'B','2010-03-29'
union all select 'B','2010-03-30'
union all select 'B','2010-03-31'
union all select 'B','2010-04-01'
union all select 'B','2010-04-02'
union all select 'B','2010-04-03'
union all select 'B','2010-04-04'
union all select 'B','2010-04-05'
union all select 'B','2010-04-06'
union all select 'B','2010-04-07'
select vcno,MIN(ddate) as dMin,dateadd(day,1,MAX(ddate)) as dMax
from(select vcno
,ddate
,datediff(day,MIN(ddate) over(partition by vcno),ddate)+1 as i
,(datediff(day,MIN(ddate) over(partition by vcno),ddate))%30+1 as i1
,ROW_NUMBER() over(partition by vcno order by ddate)  as i3
from  @a) a
group by vcno,i-i1
order by 1
vcno                 dMin       dMax
-------------------- ---------- -----------------------
A                    2010-01-01 2010-01-03 00:00:00.000
A                    2010-03-04 2010-03-05 00:00:00.000
B                    2010-03-05 2010-04-04 00:00:00.000
B                    2010-04-04 2010-04-08 00:00:00.000

Answer 8

DECLARE @TB TABLE
    (
      VCNO NVARCHAR(10) ,
      DDate DATETIME
    )
INSERT  INTO @TB
        SELECT  'A' ,
                ' 2010-01-01'
        UNION ALL
        SELECT  'A' ,
                ' 2010-01-02'
        UNION ALL
        SELECT  'A' ,
                ' 2010-03-04'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-05'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-06'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-07'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-08'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-09'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-10'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-11'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-12'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-13'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-14'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-15'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-16'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-17'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-18'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-19'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-20'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-21'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-22'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-23'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-24'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-25'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-26'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-27'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-28'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-29'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-30'
        UNION ALL
        SELECT  'B' ,
                ' 2010-03-31'
        UNION ALL
        SELECT  'B' ,
                ' 2010-04-01'
        UNION ALL
        SELECT  'B' ,
                ' 2010-04-02'
        UNION ALL
        SELECT  'B' ,
                ' 2010-04-03'
        UNION ALL
        SELECT  'B' ,
                ' 2010-04-04'
        UNION ALL
        SELECT  'B' ,
                ' 2010-04-05'
        UNION ALL
        SELECT  'B' ,
                ' 2010-04-06'
        UNION ALL
        SELECT  'B' ,
                ' 2010-04-07'                
                
SELECT  [@TB].VCNO ,
        MIN([@tb].DDate) ,
        MAX([@tb].DDate)
FROM    @TB
        INNER JOIN ( SELECT Vcno ,
                            MIN(DDate) AS MinDate ,
                            DATEPART("Dy", MIN(DDate)) AS MinDayOfYear
                     FROM   @TB
                     GROUP BY VCNO
                   ) AS A ON [@TB].VCNO = A.vcno
GROUP BY [@TB].VCNO ,
        ( ( DATEPART("dy", ddate) - a.MinDayOfYear ) - 1 ) / 30
ORDER BY [@TB].VCNO结果
VCNO minDate MaxDate
A 2010-01-01 00:00:00.000 2010-01-02 00:00:00.000
A 2010-03-04 00:00:00.000 2010-03-04 00:00:00.000
B 2010-03-05 00:00:00.000 2010-04-04 00:00:00.000
B 2010-04-05 00:00:00.000 2010-04-07 00:00:00.000