统计所有数量的方法

求一个统计所有数量的方法

表如上图所示
Date_time为varchar(12)类型
2007082023意思是2007年8月20日23点
每隔一个小时就有一个新的Rain_1hCur值。
Station_Num有很几十个，假定有60个。

现在要分别统计一年，一个月和一个周的每个Station_Num的Rain_1hCur值的总和。
求知道啊！谢谢！

[解决办法]

SQL code

--是按年,月,周统计吗?select Station_Num,left(4,Date_time) year,sum(Rain_1hCur)  Rain_1hCurfrom tbgroup by Station_Num,left(4,Date_time)order by left(4,Date_time),Station_Numgoselect Station_Num,left(6,Date_time) month,sum(Rain_1hCur)  Rain_1hCurfrom tbgroup by left(6,Date_time),Station_Numorder by left(6,Date_time),Station_Numgoselect Station_Num,left(4,Date_time),datepart(ww,convert(datetime,left(8,Date_time))) week,sum(Rain_1hCur)  Rain_1hCurfrom tbgroup by left(4,Date_time), datepart(ww,convert(datetime,left(8,Date_time))),Station_Numorder by datepart(ww,convert(datetime,left(8,Date_time))),Station_Numgo
[解决办法]
SQL code
IF OBJECT_ID('TBL')IS NOT NULLDROP TABLE TBLGOCREATE TABLE TBL(Station_Num VARCHAR(5),Date_time VARCHAR(12),Rain_1hCur NUMERIC(4,1))GOINSERT TBLSELECT '58247','2007082023',0 UNION ALLSELECT '58247','2007082100',0 UNION ALLSELECT '58247','2007082101',0 UNION ALLSELECT '58247','2007082102',0 UNION ALLSELECT '58247','2007082103',0 UNION ALLSELECT '58247','2007082104',0 UNION ALLSELECT '58247','2007082105',0 UNION ALLSELECT '58247','2007082106',0 UNION ALLSELECT '58247','2007082107',0 UNION ALLSELECT '58247','2007082108',0 UNION ALLSELECT '58247','2007082109',0 UNION ALLSELECT '58247','2007082110',0 UNION ALLSELECT '58247','2007082111',0 UNION ALLSELECT '58247','2007082112',0 UNION ALLSELECT '58247','2007082113',0 UNION ALLSELECT '58247','2007082114',0 UNION ALLSELECT '58247','2007082115',0SELECT *FROM TBLDECLARE @STARTTIME DATETIMEDECLARE @ENDTIME DATETIMESET @ENDTIME='2007-08-20'SET @ENDTIME='2007-08-21';WITH TAS(SELECT       Station_Num,CONVERT(datetime,left(Date_time,4)+'-'      +SUBSTRING(Date_time,5,2)+'-'      +SUBSTRING(Date_time,7,2)+' '+RIGHT(Date_time,2)      +':00:00.000',120) AS Date_time,Rain_1hCur      FROM TBL)--SELECT *FROM TSELECT Station_Num,SUM(Rain_1hCur) AS Rain_1hCur FROM T WHERE T.Date_time BETWEEN @STARTTIME AND @ENDTIMEGROUP BY Station_Num--我不清楚你的意念和一周是怎么个计算法，需要用参数确定就可以这样楼主写改一下吧
[解决办法]
如果我没记错的话。以前有人问过类似问题，好像是关于里程表的统计。
我的思路。先对表增加三个计算字段，年，月，日。然后数量太大的话。按年+月分表或者分区。

求每个Station_Num的Rain_1hCur当然是分组group by。
[解决办法]
SQL code
;WITH TAS(SELECT       Station_Num,CONVERT(datetime,left(Date_time,4)+'-'      +SUBSTRING(Date_time,5,2)+'-'      +SUBSTRING(Date_time,7,2)+' '+RIGHT(Date_time,2)      +':00:00.000',120) AS Date_time,Rain_1hCur      FROM TBL)--SELECT *FROM T--按年统计，统计2007年的数据：SELECT Station_Num,SUM(Rain_1hCur) AS Rain_1hCur FROM T WHERE DATEPART(YY,T.Date_time) =2007GROUP BY Station_Num--按月统计，统计8月的数据：SELECT Station_Num,SUM(Rain_1hCur) AS Rain_1hCur FROM T WHERE DATEPART(MM,T.Date_time) =08GROUP BY Station_Num--按周统计，统计第8周的数据：SELECT Station_Num,SUM(Rain_1hCur) AS Rain_1hCur FROM T WHERE DATEPART(MM,T.Date_time) =4GROUP BY Station_Num--楼主是这个意思么？
[解决办法]
SQL code
-- 按年统计select datepart(yyyy,left(Data_time,8)) 'year',Station_Num,sum(Rain_1hCur) sumRain_1hCurfrom tabgroup by datepart(yyyy,left(Data_time,8)),Station_Num-- 按月统计select datepart(mm,left(Data_time,8)) 'month',Station_Num,sum(Rain_1hCur) sumRain_1hCurfrom tabgroup by datepart(mm,left(Data_time,8)),Station_Num-- 按周统计select datepart(w,left(Data_time,8)) 'week',Station_Num,sum(Rain_1hCur) sumRain_1hCurfrom tabgroup by datepart(w,left(Data_time,8)),Station_Num