sqlserver，sql和数据如下，想将查出的数据分组

select CASE WHEN left(StartLocation,2)in('WM','BM') THEN 'BWM' ELSE left(StartLocation,2) END s,

CONVERT(NVARCHAR(10),ReqTime) as dt,ReqTime,

CONVERT(NVARCHAR(10),EndTime) as tt,

DATEDIFF(ss,ReqTime,EndTime) as gs

from dbo.test

where ReqTime is not null

and EndTime is not null

and [ReqTime] between '2023-02-14' and '2023-02-17 '

and SUBSTRING(ReqTime,12,2)>='20'and SUBSTRING(ReqTime,12,2)<'24'

and left(StartLocation,2) !='XS'

union all

select CASE WHEN left(StartLocation,2)in('WM','BM') THEN 'BWM' ELSE left(StartLocation,2) END s,

CONVERT(NVARCHAR(10),ReqTime) as dt,ReqTime,

CONVERT(NVARCHAR(10),EndTime) as tt,

DATEDIFF(ss,ReqTime,EndTime) as gs

from dbo.test

where ReqTime is not null

and EndTime is not null

and [ReqTime] between '2023-02-15' and '2023-02-18 '

and SUBSTRING(ReqTime,12,2)>='00'and SUBSTRING(ReqTime,12,2)<'08'

and left(StartLocation,2) !='XS'

查出的数据分别是14号20点到24点的数据，15号0点到8点的数据和20点到24点的数据，16号0点到8点的数据和20点到24点的数据，和17号0点到8点的数据，希望得到的结果是将每天晚八的数据和第二天早八的数据放在一起，求其gs字段的和，十四号的晚八到十五号早八的gs的和，十五号晚八到十六号早八的gs的和，十六号晚八到十七号早八的gs的和，共分为三组数据，分组后的数据不需要ReqTime字段的数据，只需要s、每组和sum(gs)的数据

数据如下：

s	dt	ReqTime	tt	gs
SM	2023/2/14	2023/2/14 21:13	02 14 2023	127
SM	2023/2/14	2023/2/14 21:15	02 14 2023	109
SM	2023/2/14	2023/2/14 21:16	02 14 2023	94
SM	2023/2/15	2023/2/15 1:08	02 15 2023	159
SM	2023/2/15	2023/2/15 1:10	02 15 2023	117
SM	2023/2/15	2023/2/15 1:11	02 15 2023	94
SM	2023/2/15	2023/2/15 22:36	02 15 2023	126
SM	2023/2/15	2023/2/15 22:38	02 15 2023	164
SM	2023/2/15	2023/2/15 22:42	02 15 2023	158
SM	2023/2/16	2023/2/16 3:27	02 16 2023	294
SM	2023/2/16	2023/2/16 3:31	02 16 2023	131
SM	2023/2/16	2023/2/16 3:32	02 16 2023	169
SM	2023/2/16	2023/2/16 23:11	02 16 2023	159
SM	2023/2/16	2023/2/16 23:15	02 16 2023	88
SM	2023/2/16	2023/2/16 23:16	02 16 2023	112
SM	2023/2/17	2023/2/17 3:22	02 17 2023	130
SM	2023/2/17	2023/2/17 3:25	02 17 2023	143
SM	2023/2/17	2023/2/17 3:26	02 17 2023	160

FineReport Vessel1213 发布于 2023-2-20 11:32

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共1回答

最佳回答

Z4u3z1Lv6专家互助
发布于2023-2-20 12:10

WITH TABLEA AS(

SELECT 'SM' as 's','2023-2-14' AS 'DT','2023-2-14 21:13' AS 'ReqTime','02 14 2023' AS 'TT','127' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-14' AS 'DT','2023-2-14 21:15' AS 'ReqTime','02 14 2023' AS 'TT','109' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-14' AS 'DT','2023-2-14 21:16' AS 'ReqTime','02 14 2023' AS 'TT','94' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-15' AS 'DT','2023-2-15 1:08' AS 'ReqTime','02 15 2023' AS 'TT','159' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-15' AS 'DT','2023-2-15 1:10' AS 'ReqTime','02 15 2023' AS 'TT','117' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-15' AS 'DT','2023-2-15 1:11' AS 'ReqTime','02 15 2023' AS 'TT','94' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-15' AS 'DT','2023-2-15 22:36' AS 'ReqTime','02 15 2023' AS 'TT','126' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-15' AS 'DT','2023-2-15 22:38' AS 'ReqTime','02 15 2023' AS 'TT','164' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-15' AS 'DT','2023-2-15 22:42' AS 'ReqTime','02 15 2023' AS 'TT','158' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-16' AS 'DT','2023-2-16 3:27' AS 'ReqTime','02 16 2023' AS 'TT','294' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-16' AS 'DT','2023-2-16 3:31' AS 'ReqTime','02 16 2023' AS 'TT','131' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-16' AS 'DT','2023-2-16 3:32' AS 'ReqTime','02 16 2023' AS 'TT','169' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-16' AS 'DT','2023-2-16 23:11' AS 'ReqTime','02 16 2023' AS 'TT','159' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-16' AS 'DT','2023-2-16 23:15' AS 'ReqTime','02 16 2023' AS 'TT','88' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-16' AS 'DT','2023-2-16 23:16' AS 'ReqTime','02 16 2023' AS 'TT','112' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-17' AS 'DT','2023-2-17 3:22' AS 'ReqTime','02 17 2023' AS 'TT','130' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-17' AS 'DT','2023-2-17 3:25' AS 'ReqTime','02 17 2023' AS 'TT','143' AS 'GS' UNION ALL

SELECT 'SM' as 's','2023-2-17' AS 'DT','2023-2-17 3:26' AS 'ReqTime','02 17 2023' AS 'TT','160' AS 'GS'

)

SELECT FLAG,SUM(CONVERT(DECIMAL(20,4),GS)) [SUM_] FROM (

SELECT *,(CASE WHEN CONVERT(DATETIME,ReqTime,120)>=FORMAT(CONVERT(DATETIME,ReqTime,120),'yyyy-MM-dd 20:00:00') THEN CONVERT(DATE,ReqTime,120) WHEN CONVERT(DATETIME,ReqTime,120)>=FORMAT(CONVERT(DATETIME,ReqTime,120),'yyyy-MM-dd 00:00:00') AND CONVERT(DATETIME,ReqTime,120)<FORMAT(CONVERT(DATETIME,ReqTime,120),'yyyy-MM-dd 09:00:00') THEN DATEADD(DAY,-1,CONVERT(DATE,ReqTime,120)) ELSE CONVERT(DATE,ReqTime,120) END ) AS 'FLAG' FROM TABLEA

) T GROUP BY FLAG