各位大侠:小弟我对SqlServer不熟悉,所以请各位大仙帮帮忙.
有个表,结构如下:
CREATE TABLE [dbo].[WarnLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DevNum] [varchar](8000) COLLATE Chinese_PRC_CI_AS NULL,
[LineABC] [varchar](8000) COLLATE Chinese_PRC_CI_AS NULL,
[DevID] [varchar](8000) COLLATE Chinese_PRC_CI_AS NULL,
[DevState] [varchar](8000) COLLATE Chinese_PRC_CI_AS NULL,
[GzParam] [varchar](8000) COLLATE Chinese_PRC_CI_AS NULL,
[ReportTime] [datetime] NULL,
CONSTRAINT [PK_WarnLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
现在我要对该表进行统计,具体要求是按DevID统计每小时,如何写sql或者函数呢?(ReportTime是时间)
有个表,结构如下:
CREATE TABLE [dbo].[WarnLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DevNum] [varchar](8000) COLLATE Chinese_PRC_CI_AS NULL,
[LineABC] [varchar](8000) COLLATE Chinese_PRC_CI_AS NULL,
[DevID] [varchar](8000) COLLATE Chinese_PRC_CI_AS NULL,
[DevState] [varchar](8000) COLLATE Chinese_PRC_CI_AS NULL,
[GzParam] [varchar](8000) COLLATE Chinese_PRC_CI_AS NULL,
[ReportTime] [datetime] NULL,
CONSTRAINT [PK_WarnLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
现在我要对该表进行统计,具体要求是按DevID统计每小时,如何写sql或者函数呢?(ReportTime是时间)
from warnlog
group by devid,convert(varchar(13),reporttime,120)
id devnum lineabc devid devstate gzparam reporttime
778942 10 A 626E DC 0 2010-1-11 12:49:43
778943 1 A 6250 C0 -6.5 2010-1-15 21:55:28
778944 1 C 6252 C0 -7 2010-1-15 21:56:14
778945 4 C 625C DA -8 2010-1-15 21:58:11
778946 2 A 6253 C0 -6.3 2010-1-15 21:58:12
778947 4 C 625C DA -8 2010-1-15 21:58:13
778948 2 C 6256 C0 -6.2 2010-1-15 21:58:53
778949 6 B 6261 C0 -7.5 2010-1-15 22:00:09
778950 26 C 62BD C1 4 2010-1-27 11:04:35
778951 26 C 62BD C1 4.5 2010-1-27 10:54:35
778952 26 C 62BD C1 5.1 2010-1-27 10:44:35
778953 26 C 62BD C1 5.1 2010-1-27 10:34:35
778954 26 C 62BD C1 5 2010-1-27 10:24:35
778955 26 C 62BD C1 4.9 2010-1-27 10:14:35
上面是数据,我传递过来的是devid,要求查询该终端当天(24小时)每个小时的信息.