我在查询中需要帮助此处的目标是按小时获取条形码的计数因此,在图像中的示例中,您可以看到从16:46到16:58的小时,因此条形码的案例计数应该会给出24 caseCount /小时的结果,依此类推,如果另一个时间从15:01到15:59开始,那么它也应该计算条形码的数量。

select CodeSearch,
count(BarCode) as countCases,
t_stamp
from dbo.stackerhistory
group by CodeSearch, t_stamp
order by CodeSearch, t_stamp发布于 2021-03-31 01:38:44
下面是你怎么做的:
select
CodeSearch,
DATEPART(HOUR,t_stamp) htime,
count(BarCode) as countCases,
t_stamp
from
dbo.stackerhistory
group by
CodeSearch,
DATEPART(HOUR,t_stamp)
order by
CodeSearch,
DATEPART(HOUR,t_stamp)这是按小时计算的,但我假设您需要按天/小时进行分组,因此您也可以按天分组:
select
CodeSearch,
DATEPART(HOUR,t_stamp) htime,
CAST(t_stamp as Date) daydate,
count(BarCode) as countCases,
t_stamp
from
dbo.stackerhistory
group by
CodeSearch,
DATEPART(HOUR,t_stamp),
CAST(t_stamp as Date)
order by
CodeSearch,
CAST(t_stamp as Date),
DATEPART(HOUR,t_stamp)发布于 2021-03-31 02:05:12
您可以将OVER window函数与count一起使用。此查询与上面的查询类似,因此它将为您提供1到1行的源。注意:此查询不区分日期,因此您可能希望限制WHERE子句中的日期。
select CodeSearch
, count(Barcode) over (partition by CodeSearch,datepart(hh,t_stamp)) as CaseCount
, t_stamp
from dbo.stackerhistoryhttps://stackoverflow.com/questions/66875664
复制相似问题