数据如下
NAME DATA
A 2008-8-11 11:30:00
B 2008-8-12 11:30:00
B 2008-8-12 11:30:00
A 2008-8-13 11:30:00
A 2008-8-13 11:30:00我想找出每天的记录总数,返回如下name data total
A 2008-8-11 1
B 2008-8-12 2
A 2008-8-13 2
NAME DATA
A 2008-8-11 11:30:00
B 2008-8-12 11:30:00
B 2008-8-12 11:30:00
A 2008-8-13 11:30:00
A 2008-8-13 11:30:00我想找出每天的记录总数,返回如下name data total
A 2008-8-11 1
B 2008-8-12 2
A 2008-8-13 2
min([name]) as [name],
[data],
count(*) as [total]
from table1
group by [data]
也就是说,如果是
B 2008-8-12 11:30:00
B 2008-8-12 11:30:30
最后统计出来的是
B 2008-8-12 2
前12 后12
还是6小时一分?
还是 上午 下午 晚上 呢?统计好做 count()就可以了 主要是条件 我是小菜鸟! 说的不好清见谅, 希望能够帮到你!
select max(name) name,max(yy)+max(mm)+max(dd) data,count(*) total
from (select NAME,datepart(DATEPART(yy,DATA) yy,datepart(DATEPART(mm,DATA) mm,datepart(DATEPART(dd,DATA) dd)
group by yy,mm,dd
select max(name) name,max(yy)+max(mm)+max(dd) data,count(*) total
from (select NAME,DATEPART(yy,DATA) yy,DATEPART(mm,DATA) mm,DATEPART(dd,DATA) dd)
group by yy,mm,dd
insert into tb select 'a','2008-8-11 11:30:00'
insert into tb select 'b','2008-8-12 11:30:00'
insert into tb select 'b','2008-8-12 11:30:00'
insert into tb select 'a','2008-8-13 11:30:00'
insert into tb select 'a','2008-8-13 11:30:00'
select name,convert(varchar(10),data,120) as data,count(1) as total
from tb
group by name,convert(varchar(10),data,120)name data total
a 2008-08-11 1
b 2008-08-12 2
a 2008-08-13 2
把时间格式化一下,比如:
[data] >>>> convert(varchar(10),[data],120) as [data] 这样就格式化时间为yyyy-mm-dd的格式了。
from tb
group by Name,ltrim(year(DATA))+'-'+ltrim(month(DATA))+'-'+ltrim(day(DATA))输出结果:
NAME DATA total
A 2008-3-11 1
B 2008-3-12 2
A 2008-3-13 2