首頁猿問 [100元話費(fèi)]來個精通數(shù)學(xué)和SQ...

[100元話費(fèi)]來個精通數(shù)學(xué)和SQL的高手指導(dǎo)下一個SQL語句

SQL Server

繁花不似錦 2018-12-07 05:42:15

--表結(jié)構(gòu) 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) --測試用數(shù)據(jù) 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個時間段，它們是一個整體，也就是說表中原有數(shù)據(jù)bianhao相同的要按整體看待?，F(xiàn)在我將要插入一行新的時間段，要求就是不能產(chǎn)生重復(fù)時間。如果重復(fù)，新增的時間段不變動，表中原有的時間將向后順延。 --測試用代碼 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; 以下內(nèi)容是我想的思路，僅供參考。先檢測時間是否存在沖突(@starttime between starttime and endtime or @endtime between starttime and endtime),存在沖突則依情況一和二來處理。情況一：starttime >=@startime，時間接著新增的時間向后移動;情況二：@starttime > starttime and @starttime <=endtime.將沖突所在行截為兩段。

查看完整描述

7 回答

臨摹微笑

TA貢獻(xiàn)1982條經(jīng)驗(yàn) 獲得超2個贊

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

反對回復(fù) 2019-01-07