请教个时间段统计的问题

[zbking]

ID DATETIME VAR1
1 2011-1-1 12:10:10 1
2 2011-1-1 12:10:10 2
3 2011-1-2 12:10:10 3
4 2011-1-3 12:10:10 4
5 2011-1-3 12:10:10 5

期待如下结果：简单来讲就是日期汇总
2011-1-1 2 3
2011-1-2 1 3
2011-1-3 2 9

[SylarZhou]

select CONVERT(varchar(10),[Datetime],112), count(datetime) from  table  group by  CONVERT(varchar(10),[Datetime],112)  后面那个数字没看懂你是在统计啥

[SylarZhou]

唉 抢分不容易啊

[yhui1989love]

太不容易了的说！！！！！

[大明湖畔_帅锅]

[Quote=引用 2 楼 hj_daxian 的回复:]

SQL code

select to_char(DATETIME,'yyyy-mm-dd') ,count(VAR1),sum(VAR1)
from tb1
group by to_char(DATETIME,'yyyy-mm-dd')
[/Quote]

忘了 这是sql区
to_char(DATETIME,'yyyy-mm-dd') → convert(varchar(10),datetime,120)

[tianyazaixian]

我也看看 。。。

[dawugui]

select convert(varchar(10),DATETIME,120) DATETIME , count(1) cnt , sum(var1) [sum]
from tb
gruop by convert(varchar(10),DATETIME,120)

[pengxuan]

if object_id('tb') is not null
   drop table tb
go
create table tb
(
 id int identity(1,1),
 [datetime] datetime,
 var1 int
)
go
insert into tb([datetime],var1)
select '2011-1-1 12:10:10',1 union all
select '2011-1-1 12:10:10',2 union all
select '2011-1-2 12:10:10',3 union all
select '2011-1-3 12:10:10',4 union all
select '2011-1-3 12:10:10',5
go
select convert(varchar(10),[datetime],120),sum(var1) from tb group by convert(varchar(10),[datetime],120)
go
/*
           
---------- -----------
2011-01-01 3
2011-01-02 3
2011-01-03 9

(3 行受影响)

*/

[大明湖畔_帅锅]

select to_char(DATETIME,'yyyy-mm-dd') ,count(VAR1),sum(VAR1) 
from tb1 
group by to_char(DATETIME,'yyyy-mm-dd')

[pengxuan]

--改一下
if object_id('tb') is not null
   drop table tb
go
create table tb
(
 id int identity(1,1),
 [datetime] datetime,
 var1 int
)
go
insert into tb([datetime],var1)
select '2011-1-1 12:10:10',1 union all
select '2011-1-1 12:10:10',2 union all
select '2011-1-2 12:10:10',3 union all
select '2011-1-3 12:10:10',4 union all
select '2011-1-3 12:10:10',5
go
select [datetime]=convert(varchar(10),[datetime],120),cnt=count(*),sum(var1) from tb group by convert(varchar(10),[datetime],120)
go
/*
datetime   cnt         
---------- ----------- -----------
2011-01-01 2           3
2011-01-02 1           3
2011-01-03 2           9

(3 行受影响)
*/

[pengxuan]

2楼到1楼的前面了

[-晴天]

create table tb(ID int,[DATETIME] datetime,VAR1 int)
insert into tb select 1,'2011-1-1 12:10:10',1   
insert into tb select 2,'2011-1-1 12:10:10',2   
insert into tb select 3,'2011-1-2 12:10:10',3   
insert into tb select 4,'2011-1-3 12:10:10',4   
insert into tb select 5,'2011-1-3 12:10:10',5
go
select CONVERT(varchar(10),[datetime],120)dt ,COUNT(*)ct,SUM(var1)s
from tb
group by CONVERT(varchar(10),[datetime],120)
/*
dt         ct          s
---------- ----------- -----------
2011-01-01 2           3
2011-01-02 1           3
2011-01-03 2           9

(3 行受影响)

*/
go
drop table tb

[jwdream2008]

select convert(char(8),[DATETIME],114) date
,count()
,sum(VAR1)
from TB
group by convert(char(8),[DATETIME],114) 

[--小F--]

select
  convert(varchar(10),datetime,120) ,count(1),sum(var1)
from
  tb
group by
   convert(varchar(10),datetime,120)