-- 再来吹吹：游标(进者有分) --

luoyoumou 2010-06-21 07:52:55

-- 火车不是推的，游标不是吹的!( 哪位SQL大师再来用6行代码敲定试试？)
http://topic.csdn.net/u/20100621/12/066bd0e9-aebe-47c5-841b-0ba248da137d.html?4199

------------------------------- 搭建测试环境：-------------------------------------------------------

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; -- 重置 @old_dDate

        SET @new_dDate = @dDate; -- 重置 @new_dDate

	SET @v_count = 1;        -- 重置 @v_count 

      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;