SQL怎样统计每天早上8：00到次日早上8：00所有的数据?

[宅宅猫]

需求实现：随便取一段日期：2022-3-4——2022-3-17

如何按天分别统计出每天早上8：00到次日早上8：00所有的数据?

如2022-3-4日20:00后数据——2022-3-5日早上8点前的数据加起来

一天的还可以算，但是一段日期连起来就不知道怎么加了。

[幸福感奇差]

可以把当前日期减 8 小时作为计算列，根据这个计算列分天聚合

[shinger126]

select cast(dateadd(hh,-8,日期) as date) 日期 from table

[RINK_1]

CREATE TABLE #T
(ID INT IDENTITY(1,1),
 DT DATETIME)

INSERT INTO #T
SELECT'2022-04-06 8:00:00'  UNION ALL
SELECT'2022-04-06 18:20:00'  UNION ALL
SELECT'2022-04-06 22:30:00'  UNION ALL
SELECT'2022-04-07 07:59:00'  UNION ALL
SELECT'2022-04-07 08:01:00'  UNION ALL
SELECT'2022-04-07 08:30:00'  UNION ALL
SELECT'2022-04-08 06:00:00'  UNION ALL
SELECT'2022-04-12 12:06:00'  UNION ALL
SELECT'2022-04-12 23:59:00'  UNION ALL
SELECT'2022-04-13 07:59:00'  UNION ALL
SELECT'2022-04-13 08:00:00'  UNION ALL
SELECT'2022-04-15 10:50:00'  UNION ALL
SELECT'2022-04-15 17:00:00'  UNION ALL
SELECT'2022-04-16 00:01:00'  UNION ALL
SELECT'2022-04-16 06:59:00' UNION ALL
SELECT'2022-04-16 07:59:00' UNION ALL
SELECT'2022-04-16 08:00:00' UNION ALL
SELECT '2022-04-16 08:01:00'


SELECT DT_NEW,COUNT(*) AS AMOUNT
FROM 
(SELECT *,CONVERT(DATE,CONVERT(VARCHAR(10),DATEADD(HOUR,-8,DT),120)) AS DT_NEW FROM #T) AS A
WHERE DT_NEW BETWEEN '2022-04-02' AND '2022-04-15'
GROUP BY DT_NEW

[morliz子轩]

这个其实不难，关键点是每一个datetime 值都需要先去判断是否在昨天晚8点后和今天早点前的范围。
所以要有个循环遍历，去获取每一个值再去检查判断，返回要的数据结果。

--模拟数据
declare @dataTab table (
    id int identity(1,1) not null,
    dtPara datetime
)
insert into @dataTab
select '2022-3-4 7:00:00' union all
select '2022-3-5 19:00:00' union all
select '2022-3-6 21:00:00' union all
select '2022-3-7 22:00:00' union all
select '2022-3-8 7:00:00' union all
select '2022-3-9 15:00:00' union all
select '2022-3-10 18:00:00' union all
select '2022-3-11 23:00:00' union all
select '2022-3-12 1:00:00' union all
select '2022-3-13 2:00:00' union all
select '2022-3-14 3:00:00' union all
select '2022-3-15 4:00:00' union all
select '2022-3-16 5:00:00' union all
select '2022-3-17 6:00:00'

--select * from @dataTab where dtPara between '2022-3-4 20:00' and '2022-3-5 8:00'
----------------------------------------------------------------------------------------------
--参数体
declare @dtBegin nvarchar(10) ='2022-3-4',
        @dtEnd nvarchar(10) ='2022-3-17',
        @BeginAM nvarchar(6) ='8:00',
        @EndinPM nvarchar(6) ='20:00'
-- select convert(datetime,@dtBegin +' '+ @BeginAM)

--变量体
declare @minId int,
        @maxId int
        
--结果集
declare @Result table (
    id int identity(1,1) not null,
    dtPara datetime    
)

        
select @minId = min(id), @maxId =max (id) from @dataTab
-- select @minId,@maxId
while (@minId is not null and @minId <= @maxId)
begin
    insert into @Result
    select dtPara from @dataTab    where id = @minId 
        and dtPara between convert(datetime,@dtBegin +' '+ @BeginAM) and convert(datetime,@dtEnd +' '+ @EndinPM)
    
    set @minId = @minId+1
end

select * from @Result