高分跪求一SQL查询语句，急急急
我有一个表main内容如下
date name total
2006-03-01 07:20:00 adn 20
2006-03-01 07:25:00 adf 40
2006-03-01 09:20:00 df 20
2006-03-02 07:20:00 dfsd 10
2006-03-02 05:20:00 df 2.3
2006-03-01 05:20:00 dsf 2.4
2006-03-03 07:20:00 sdf 2.64
我想统计2006-03-01到2006-03-03这三天每天的记录数怎么弄,并把每天的都汇总显示
我的查询语句为
select count(date) from main date>=2006-03-01 00:00:00 date<=2006-03-03 23:29:29 group by date
汇总出为的数据为
1
1
1
1
1
1
了可我想的是把每天的记录数分别汇总，按照我的要求应该为
4
2
1
请问该怎么写代码啊
问题点数：20、回复次数：6Top
1 楼filebat（Mark）回复于 2006-03-05 15:30:45 得分 5
select 日期=convert(char(10), date, 120), 记录数=count(1)
from tt
where date between '2006-03-01 00:00:00 ' and '2006-03-03 23:29:29'
group by convert(char(10), date, 120)Top
2 楼filebat（Mark）回复于 2006-03-05 15:31:42 得分 0
--对于sqlserver数据库,下面这个是OK的
create table tt(date datetime, name varchar(10), total float)
go
insert tt select '2006-03-01 07:20:00', 'adn', 20
union all select '2006-03-01 07:25:00', 'adf', 40
union all select '2006-03-01 09:20:00', 'df', 20
union all select '2006-03-02 07:20:00', 'dfsd',10
union all select '2006-03-02 05:20:00', 'df', 2.3
union all select '2006-03-01 05:20:00', 'dsf', 2.4
union all select '2006-03-03 07:20:00', 'sdf', 2.64
go
select 日期=convert(char(10), date, 120), 记录数=count(1)
from tt
where date between '2006-03-01 00:00:00 ' and '2006-03-03 23:29:29'
group by convert(char(10), date, 120)
Top
3 楼hemhem（半斤）回复于 2006-03-07 16:58:43 得分 0
group by 写错了你Top
4 楼cobejordan（非菲非霏）回复于 2006-03-09 14:44:10 得分 5
说明使用的数据库，才能给你准确SQL。date是关键字，避免使用。
设cast()是转换字段类型的函数，思路如下：
select cast(日期 as date 'YYYYMMDD'), count(*)
from main
where cast(日期 as date 'YYYYMMDD') between '20060301' and '20060303'
group by 1Top
5 楼kaple（纸叶子）回复于 2006-03-16 17:23:31 得分 5
select count(date) from main
where date>=2006-03-01 00:00:00 date<=2006-03-03 23:29:29
group by to_char(date,'yyyymmdd')
Top
6 楼scounix505（mery）回复于 2006-03-22 11:46:13 得分 5
oracle中语句：
select to_char(date,1,8) date,count(*),sum(total) from main group by to_char(date,1,8) order by to_char(date,1,8)
Top
