如何用sql语句查询出当月的每日数据量

[greyrainbow2010]

表里数据如下
id time count
1 2012-12-1 02:00:00 42
1 2012-12-1 14:18:12 79
1 2012-12-10 07:15:42 112
1 2012-12-14 10:06:20 57
1 2012-12-14 11:54:10 124
1 2012-12-14 14:10:27 241
.............................

我要统计出当月每天的count数之和（比如12月1日是 42+79= 121）
当月（比如12月份有31天，我要统计出这31天每天的的count数之和）

sql 语句该怎么写？
这里有个情况，比如12月份，有31天，就是说要取出31条数据，但数据表内可能没有12月7日的数据，检索结果中也要有12月7日的记录集

sql 返回的字段应该是如下格式的
12-1 121
12-2
12-3
...
12-10 112
这样子的，谁帮忙写一个？？？

[zhengnan2012]

select cast(time as date),sum(count) from tb group by cast(time as date) 试试看行不。

[jiangshun]

--> 测试数据：[TB] if object_id('[TB]') is not null drop table [TB] GO create table [TB]([id] int,[time] datetime,[count] int) insert [TB] select 1,'2012-12-1 02:00:00',42 union all select 1,'2012-12-1 14:18:12',79 union all select 1,'2012-12-10 07:15:42',112 union all select 1,'2012-12-14 10:06:20',57 union all select 1,'2012-12-14 11:54:10',124 union all select 1,'2012-12-14 14:10:27',241 DECLARE @currentMonth INT SET @currentMonth=12 DECLARE @startDate DATETIME,@endDate DATETIME SET @startDate=DATENAME(yy,GETDATE())+'-'+RTRIM(@currentMonth)+'-01' SET @endDate=DATEADD(dd,-1,DATEADD(mm,1,@startDate)) select DATEADD(dd,number,@startDate),SUM(ISNULL([count],0)) FROM [master].dbo.spt_values sv LEFT JOIN tB t ON CONVERT(VARCHAR(10),t.[time],120)=CONVERT(VARCHAR(10),DATEADD(dd,number,@startDate),120) WHERE sv.[type]='p' AND DATEADD(dd,number,@startDate)<@endDate GROUP BY DATEADD(dd,number,@startDate) ORDER BY 1 ASC drop table [TB]

[greyrainbow2010]

引用 2 楼 jiangshun 的回复:

--> 测试数据：[TB] if object_id('[TB]') is not null drop table [TB] GO create table [TB]([id] int,[time] datetime,[count] int) insert [TB] select 1,'2012-12-1 02:00:00',42 union all select 1,'2012-12……

不用存储过程能用一句sql语句写出来吗？

[feisheng512]

--模拟数据表
 create table [TB]([id] int,[time] datetime,[count] int)
 --添加模拟数据
 insert [TB]
 select 1,'2012-12-01 02:00:00',42 union all
 select 1,'2012-12-01 14:18:12',79 union all
 select 1,'2012-12-10 07:15:42',112 union all
 select 1,'2012-12-14 10:06:20',57 union all
 select 1,'2012-12-14 11:54:10',124 union all
 select 1,'2012-12-14 14:10:27',241
 
 --根据日前的月份和天数分组求和，
 --条件为当前的月份转换成2012-12-14 00:00:00 的时间格式
 --month()、day()函数返回值为整型，故转换为字符后拼接
 --数据格式为2012-12-14 00:00:00可正常运行
 --如果没有12号数据则不统计
 SELECT CAST(MONTH([time]) AS VARCHAR(4))+'-'+RIGHT('00'+CAST(DAY([time]) AS VARCHAR(4)),2) AS [time],
	SUM([count]) AS SumCount FROM TB
	WHERE  CONVERT(VARCHAR(7),[time],120)=CONVERT(VARCHAR(7),GETDATE(),120)
	GROUP BY MONTH([time]),DAY([time])
--删除模拟数据
DROP TABLE 	TB
--结果
time      SumCount
--------- -----------
12-01     121
12-10     112
12-14     422

[feisheng512]

--模拟数据表
 create table [TB]([id] int,[time] datetime,[count] int)
 --添加模拟数据
 insert [TB]
 select 1,'2012-12-01 02:00:00',42 union all
 select 1,'2012-12-01 14:18:12',79 union all
 select 1,'2012-12-10 07:15:42',112 union all
 select 1,'2012-12-14 10:06:20',57 union all
 select 1,'2012-12-14 11:54:10',124 union all
 select 1,'2012-12-14 14:10:27',241
 
--声明空白数据
DECLARE @tbnull TABLE(id INT,[time] DATETIME,[count] INT)
--递增变量
DECLARE @day INT
SET @day=0
--当月天数
DECLARE @days INT
SELECT @days= day(dateadd(mm,1,getdate())-day(getdate())) 
--循环插入空白数据
WHILE (@day<@days)
BEGIN
SET @day=@day+1
INSERT INTO @tbnull
        ( id, time, count )
VALUES  ( 1,CONVERT(DATETIME,CONVERT(VARCHAR(8),GETDATE(),120)+RIGHT('00'+@day,2)),0)
END;
 --合并数据
 WITH data AS
 (
	SELECT * FROM TB
	UNION ALL 
	SELECT * FROM @tbnull
 )
 --根据日前的月份和天数分组求和，
 --条件为当前的月份转换成2012-12-14 00:00:00 的时间格式
 --month()、day()函数返回值为整型，故转换为字符后拼接
 --数据格式为2012-12-14 00:00:00可正常运行
 SELECT CAST(MONTH([time]) AS VARCHAR(4))+'-'+RIGHT('00'+CAST(DAY([time]) AS VARCHAR(4)),2) AS [time],
	SUM([count]) AS SumCount FROM data
	WHERE  CONVERT(VARCHAR(7),[time],120)=CONVERT(VARCHAR(7),GETDATE(),120)
	GROUP BY MONTH([time]),DAY([time])
--删除模拟数据
DROP TABLE 	TB
--结果
time      SumCount
--------- -----------
12-01     121
12-02     0
12-03     0
12-04     0
12-05     0
12-06     0
12-07     0
12-08     0
12-09     0
12-10     112
12-11     0
12-12     0
12-13     0
12-14     422
12-15     0
12-16     0
12-17     0
12-18     0
12-19     0
12-20     0
12-21     0
12-22     0
12-23     0
12-24     0
12-25     0
12-26     0
12-27     0
12-28     0
12-29     0
12-30     0
12-31     0