[100元话费]来个精通数学和SQL的高手指导下一个SQL语句

Question

--表结构 CREATE TABLE [dbo].[ERPZJPhaseTime]( [id] [int] IDENTITY(1,1) NOT NULL, [bianhao] [int] NOT NULL, [phase] [int] NULL, [phasename] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NULL, [starttime] [smalldatetime] NULL, [endtime] [smalldatetime] NULL, [days] [int] NULL, [bzstate] [int] NULL, [shstate] [int] NULL, [AchieveState] [int] NULL, [category] [int] NULL, [orderid] [int] NULL) --测试用数据 insert ERPZJPhaseTime (id,bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid) values ( 2,2,1,NULL,'2012-07-04 00:00:00.000','2012-07-05 00:00:00.000',2,NULL,NULL,NULL,2,1) insert ERPZJPhaseTime (id,bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid) values ( 3,2,2,NULL,'2012-07-06 00:00:00.000','2012-07-08 00:00:00.000',3,NULL,NULL,NULL,2,1) insert ERPZJPhaseTime (id,bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid) values ( 4,2,3,NULL,'2012-07-10 00:00:00.000','2012-07-13 00:00:00.000',4,NULL,NULL,NULL,2,1) 要求：表中已有bianhao=2的3个时间段，它们是一个整体，也就是说表中原有数据bianhao相同的要按整体看待。现在我将要插入一行新的时间段，要求就是不能产生重复时间。如果重复，新增的时间段不变动，表中原有的时间将向后顺延。 --测试用代码 declare @starttime smalldatetime ,@endtime smalldatetime ,@days int ,@category int ,@effectrow int set @starttime ='2012/07/03';set @endtime='2012/07/05';set @days=3; set @bianhao=2; 以下内容是我想的思路，仅供参考。 先检测时间是否存在冲突(@starttime between starttime and endtime or @endtime between starttime and endtime),存在冲突则依情况一和二来处理。 情况一：starttime >=@startime，时间接着新增的时间向后移动;情况二：@starttime > starttime and @starttime <=endtime.将冲突所在行截为两段。

Answer 1

StartFragment

declare  @starttime1 smalldatetime ,@endtime1 smalldatetime ,@days1 int ,@bianhao1 INT;
set @starttime1 ='2012/07/04';set @endtime1='2012/07/06';set @days1=3; set @bianhao1=2;

    DECLARE @id INT;
    DECLARE @bianhao int;
    DECLARE @phase int;
    DECLARE @phasename nvarchar(200);
    DECLARE @starttime smalldatetime;
    DECLARE @endtime smalldatetime;
    DECLARE @days int;
    DECLARE @bzstate int;
    DECLARE @shstate int;
    DECLARE @AchieveState int;
    DECLARE @category int;
    DECLARE @orderid INT;
    DECLARE @OP INT;
    DECLARE @NUM INT;
    DECLARE @pendtime smalldatetime;
    SET @OP=0;
    SET @NUM=0;
    DECLARE C1 CURSOR
FOR SELECT * FROM ERPZJPhaseTime WHERE bianhao=2 ORDER BY starttime
OPEN C1;
FETCH NEXT FROM C1 INTO @id,@bianhao,@phase,@phasename,@starttime,@endtime,@days,@bzstate,@shstate,@AchieveState,@category,@orderid
 WHILE @@FETCH_STATUS=0
 BEGIN
 IF @OP>0
 BEGIN
 IF @NUM>0
 BEGIN
   UPDATE ERPZJPhaseTime SET starttime=DATEADD(DAY,@NUM,@starttime),endtime=DATEADD(DAY,@NUM,@endtime) WHERE id=@id;
 END
 END 
 ELSE IF @OP<=0
 BEGIN
  IF DATEDIFF(dd,@starttime1,@starttime)>=0
  BEGIN
    INSERT INTO ERPZJPhaseTime (bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid)  values ( @bianhao1,NULL,NULL,@starttime1,@endtime1,@days1,NULL,NULL,NULL,2,1);
SET @OP=1;
IF DATEDIFF(dd,@endtime1,@starttime)<=0
BEGIN
  SET @NUM=DATEDIFF(dd,@starttime,@endtime1)+1;
  UPDATE ERPZJPhaseTime SET starttime=DATEADD(DAY,@NUM,@starttime),endtime=DATEADD(DAY,@NUM,@endtime) WHERE id=@id;
  SET @OP=1;
END
  END
  ELSE IF (DATEDIFF(dd,@starttime1,@starttime)<0 AND DATEDIFF(dd,@starttime1,@endtime)>=0)
  BEGIN
    INSERT INTO ERPZJPhaseTime (bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid)  values ( @bianhao1,NULL,NULL,@starttime1,@endtime1,@days1,NULL,NULL,NULL,2,1)
SET @OP=1;
UPDATE ERPZJPhaseTime SET endtime=DATEADD(DAY,-1,@starttime1),days=DATEDIFF(dd,starttime,DATEADD(DAY,-1,@starttime1))+1 WHERE id=@id;
SET @NUM=@days1+DATEDIFF(dd,@starttime1,@endtime)+1;
INSERT INTO ERPZJPhaseTime (bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid)  values (@bianhao,@phase,@phasename,DATEADD(DAY,@days,@starttime1),DATEADD(DAY,@days,@endtime),DATEDIFF(dd,DATEADD(DAY,@days,@starttime1),DATEADD(DAY,@days,@endtime))+1,@bzstate,@shstate,@AchieveState,@category,@orderid)
  END
  
 END

 FETCH NEXT FROM C1 INTO @id,@bianhao,@phase,@phasename,@starttime,@endtime,@days,@bzstate,@shstate,@AchieveState,@category,@orderid
 END
CLOSE C1;
DEALLOCATE C1;

SELECT * FROM ERPZJPhaseTime WHERE bianhao=2 ORDER BY starttime

Answer 2

呵呵,太复杂了,大家都懒得看

Answer 3

变成 (1,1) (2,6) (7,8) (9,11)