c# – sql查询计算每小时的数据输入

发布时间：2020-12-15 22:32:08 所属栏目：百科来源：网络整理

导读：我想显示特定行的每小时(1小时时间间隔)内当前数据输入的计数,最后显示所有小时的累计. 表详情： CREATE TABLE [dbo].[ProductTable] ( [Id] INT IDENTITY (1,1) NOT NULL,[ProductID] INT NOT NULL,[EmployeeID] INT NOT NULL,[Operation] VARCHAR (50) NOT

我想显示特定行的每小时(1小时时间间隔)内当前数据输入的计数,最后显示所有小时的累计.
表详情：

CREATE TABLE [dbo].[ProductTable] (
    [Id]         INT          IDENTITY (1,1) NOT NULL,[ProductID]  INT          NOT NULL,[EmployeeID] INT          NOT NULL,[Operation]  VARCHAR (50) NOT NULL,[Section]    NCHAR (10)   NOT NULL,[Line]       INT          NOT NULL,[Date]       DATETIME     DEFAULT (getdate()) NULL,PRIMARY KEY CLUSTERED ([Id] ASC),);

我正在使用Microsoft Visual Studio SQL服务器
我写了这么多,但不知道每小时都要概括一下

SELECT COUNT(Id) AS Expr1
FROM ProductTable
WHERE (Line = 2)
AND (CONVERT(VARCHAR(10),Date,105) = CONVERT(VARCHAR(10),GETDATE(),105))
AND (DATEPART(hour,GETDATE()) BETWEEN 9 AND 10)`

ProductTable数据

ProductTable Data

使用“案例”时出错

Error on using case

解决方法

听起来你想按小时分组

SELECT      DATEPART(hour,date) as TimeOfDay,COUNT(Id) AS Entries
FROM            ProductTable
WHERE        (Line = 2) AND cast ([date] as date) =cast (getdate() as date) 
GROUP BY DATEPART(hour,date)

要获得一天中所有小时的累计,请使用分组集

SELECT       ISNULL(cast(DATEPART(hour,[date]) as varchar(5)),'Total') as TimeOfDay,COUNT(Id) AS Entries
        FROM            ProductTable
        WHERE        (Line = 2) AND cast ([date] as date) =cast (getdate() as date)  
        GROUP BY GROUPING SETS (DATEPART(hour,[date]),())
ORDER BY   ISNULL(cast(DATEPART(hour,'Total')

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!