temp为各个时间段的售书情况,
统计每天各类书的售书总量,比如'语文'在20101001日的总量为5;'数学'为7CREATE TABLE [dbo].[temp](
[id] [int] IDENTITY(1,1) NOT NULL,
[bookname] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[counts] [int] NULL,
[time] [datetime] NULL
) ON [PRIMARY]
go
insert into temp(bookname,counts,time)
select '语文',1,'2010-10-1 00:00:00'
union
select '语文',1,'2010-10-1 01:00:00'
union
select '语文',2,'2010-10-1 02:00:00'
union
select '语文',1,'2010-10-1 03:00:00'
union
select '语文',3,'2010-10-2 06:00:00'
union
select '数学',2,'2010-10-1 07:00:00'
union
select '数学',2,'2010-10-1 08:00:00'
union
select '数学',3,'2010-10-1 09:00:00'
union
select '数学',10,'2010-10-3 07:00:00'
union
select '数学',5,'2010-10-3 08:00:00'
union
select '英语',7,'2010-10-1 09:00:00'
union
select '英语',7,'2010-10-8 10:00:00'
统计每天各类书的售书总量,比如'语文'在20101001日的总量为5;'数学'为7CREATE TABLE [dbo].[temp](
[id] [int] IDENTITY(1,1) NOT NULL,
[bookname] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[counts] [int] NULL,
[time] [datetime] NULL
) ON [PRIMARY]
go
insert into temp(bookname,counts,time)
select '语文',1,'2010-10-1 00:00:00'
union
select '语文',1,'2010-10-1 01:00:00'
union
select '语文',2,'2010-10-1 02:00:00'
union
select '语文',1,'2010-10-1 03:00:00'
union
select '语文',3,'2010-10-2 06:00:00'
union
select '数学',2,'2010-10-1 07:00:00'
union
select '数学',2,'2010-10-1 08:00:00'
union
select '数学',3,'2010-10-1 09:00:00'
union
select '数学',10,'2010-10-3 07:00:00'
union
select '数学',5,'2010-10-3 08:00:00'
union
select '英语',7,'2010-10-1 09:00:00'
union
select '英语',7,'2010-10-8 10:00:00'
select bookname,sum(counts),cast(time as date) as date
from temp group by cast(time as date)
CREATE TABLE #tb(
[id] [int] IDENTITY(1,1) NOT NULL,
[bookname] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[counts] [int] NULL,
[time] [datetime] NULL
) ON [PRIMARY]
go
insert into #tb(bookname,counts,time)
select '语文',1,'2010-10-1 00:00:00'
union
select '语文',1,'2010-10-1 01:00:00'
union
select '语文',2,'2010-10-1 02:00:00'
union
select '语文',1,'2010-10-1 03:00:00'
union
select '语文',3,'2010-10-2 06:00:00'
union
select '数学',2,'2010-10-1 07:00:00'
union
select '数学',2,'2010-10-1 08:00:00'
union
select '数学',3,'2010-10-1 09:00:00'
union
select '数学',10,'2010-10-3 07:00:00'
union
select '数学',5,'2010-10-3 08:00:00'
union
select '英语',7,'2010-10-1 09:00:00'
union
select '英语',7,'2010-10-8 10:00:00'select cast(time as date) as date,bookname,sum(counts)
from #tb group by cast(time as date),bookname
类型 date 不是已定义的系统类型。
消息 243,级别 16,状态 1,第 1 行
类型 date 不是已定义的系统类型。
类型 time 不是已定义的系统类型。
消息 207,级别 16,状态 1,第 1 行
列名 'count' 无效。
下面就行了
select CONVERT(char(10),time,20) as date,bookname,sum(counts)
from #tb group by CONVERT(char(10),time,20),bookname