CREATE TABLE testtable(ID int identity(1,1), NNAME VARCHAR(20), begintime DATEtime, endtime DATEtime);
INSERT INTO testtable
VALUES('bm1','2021-01-10 20:57:02','2021-01-11 10:08:29')
,('bm2','2021-01-08 17:23:17','2021-01-10 11:54:23')
select * from testtable
ID NNAME begintime endtime
1 bm1 2021-01-10 20:57:02.000 2021-01-11 10:08:29.000
2 bm2 2021-01-08 17:23:17.000 2021-01-10 11:54:23.000
按照7:30 到 19:30 為白班 ，19:30 到7:30為晚班拆分為：
ID NNAME begintime endtime remark
1 bm1 2021-01-10 20:57:02.000 2021-01-11 06:59:59.000 2021-01-10 晚班
2 bm1 2021-01-11 07:30:00.000 2021-01-11 10:08:29.000 2021-01-11 白班
3 bm2 2021-01-08 17:23:17.000 2021-01-08 18:59:59.000 2021-01-08 白班
4 bm2 2021-01-08 19:30:00.000 2021-01-09 06:59:59.000 2021-01-08 晚班
5 bm2 2021-01-09 07:30:00.000 2021-01-09 18:59:59.000 2021-01-09 白班
6 bm2 2021-01-09 19:30:00.000 2021-01-10 06:59:59.000 2021-01-09 晚班
7 bm2 2021-01-10 07:30:00.000 2021-01-10 11:54:23.000 2021-01-10 白班
sql陳述句怎么拆分，求大神幫解
uj5u.com熱心網友回復：
CREATE TABLE #A(ID int identity(1,1), NNAME VARCHAR(20), begintime DATEtime, endtime DATEtime);
INSERT INTO #A
VALUES('bm1','2021-01-10 20:57:02','2021-01-11 10:08:29')
,('bm2','2021-01-08 17:23:17','2021-01-10 11:54:23')
select * from #A
CREATE TABLE #B(ID int, NNAME VARCHAR(20), begintime DATEtime, endtime DATEtime);
DECLARE @BEGIN DATETIME,@END DATETIME,@SQL VARCHAR(MAX),@I INT=1,@NAME VARCHAR(20),@TIME DATETIME,@TIME1 DATETIME
WHILE(@I<=(SELECT MAX(ID) FROM #A))
BEGIN
SELECT @BEGIN=BEGINTIME,@END = ENDTIME,@NAME = NNAME FROM #A WHERE ID = @I
SET @TIME = LEFT(DATEADD(DD,-1,@BEGIN),11)+ '19:30:00'
WHILE (@TIME<@END)
BEGIN
SET @TIME1 = @TIME
SET @TIME = DATEADD(SS,41399,@TIME)
INSERT INTO #B VALUES(@I,@NAME,@TIME1,@TIME)
SET @TIME1 = @TIME
SET @TIME = DATEADD(SS,1801,@TIME)
END
UPDATE #B SET begintime = @BEGIN WHERE NNAME = @NAME AND ID= @I AND @BEGIN BETWEEN begintime AND endtime
DELETE #B WHERE NNAME = @NAME AND ID= @I AND endtime < @BEGIN
UPDATE #B SET endtime = @END WHERE NNAME = @NAME AND ID= @I AND @END BETWEEN begintime AND endtime
SET @I=@I+1
END
SELECT *,LEFT((CONVERT(VARCHAR(30),begintime,21)),11)+' '+(CASE WHEN CAST(begintime AS TIME) BETWEEN '07:30:00' AND '18:59:59.000' THEN '白班' ELSE '晚班' END) REMARK FROM #B ORDER BY ID,begintime
DROP TABLE #B
DROP TABLE #A
寫了一個復雜的方法拋磚引玉，等明天大佬起床回答吧轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/248787.html
標籤：疑難問題