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

[chaoliu1024]

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

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

[开着拖拉机泡妞]

时间是连续的么？还有后面的那一列怎么都是0

[kingtiy]

--是按年,月,周统计吗?
select Station_Num,left(4,Date_time) year,sum(Rain_1hCur)  Rain_1hCur
from tb
group by Station_Num,left(4,Date_time)
order by left(4,Date_time),Station_Num
go
select Station_Num,left(6,Date_time) month,sum(Rain_1hCur)  Rain_1hCur
from tb
group by left(6,Date_time),Station_Num
order by left(6,Date_time),Station_Num
go
select Station_Num,left(4,Date_time),datepart(ww,convert(datetime,left(8,Date_time))) week,sum(Rain_1hCur)  Rain_1hCur
from tb
group by left(4,Date_time), datepart(ww,convert(datetime,left(8,Date_time))),Station_Num
order by datepart(ww,convert(datetime,left(8,Date_time))),Station_Num
go

[chaoliu1024]

[Quote=引用 1 楼 travylee 的回复:]
时间是连续的么？还有后面的那一列怎么都是0
[/Quote]时间是连续的，数据量很大，只是截了一部分图，这部分刚好都为零，有不为零的

[开着拖拉机泡妞]

IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
Station_Num VARCHAR(5),
Date_time VARCHAR(12),
Rain_1hCur NUMERIC(4,1)
)

GO
INSERT TBL
SELECT '58247','2007082023',0 UNION ALL
SELECT '58247','2007082100',0 UNION ALL
SELECT '58247','2007082101',0 UNION ALL
SELECT '58247','2007082102',0 UNION ALL
SELECT '58247','2007082103',0 UNION ALL
SELECT '58247','2007082104',0 UNION ALL
SELECT '58247','2007082105',0 UNION ALL
SELECT '58247','2007082106',0 UNION ALL
SELECT '58247','2007082107',0 UNION ALL
SELECT '58247','2007082108',0 UNION ALL
SELECT '58247','2007082109',0 UNION ALL
SELECT '58247','2007082110',0 UNION ALL
SELECT '58247','2007082111',0 UNION ALL
SELECT '58247','2007082112',0 UNION ALL
SELECT '58247','2007082113',0 UNION ALL
SELECT '58247','2007082114',0 UNION ALL
SELECT '58247','2007082115',0

SELECT *FROM TBL

DECLARE @STARTTIME DATETIME
DECLARE @ENDTIME DATETIME
SET @ENDTIME='2007-08-20'
SET @ENDTIME='2007-08-21'
;WITH T
AS
(
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
SELECT Station_Num,SUM(Rain_1hCur) AS Rain_1hCur 
FROM T WHERE T.Date_time BETWEEN @STARTTIME AND @ENDTIME
GROUP BY Station_Num


--我不清楚你的意念和一周是怎么个计算法，需要用参数确定就可以这样
楼主写改一下吧

[chengg0769]

如果我没记错的话。以前有人问过类似问题，好像是关于里程表的统计。
我的思路。先对表增加三个计算字段，年，月，日。然后数量太大的话。按年+月分表或者分区。

求每个Station_Num的Rain_1hCur当然是分组group by。

[开着拖拉机泡妞]

;WITH T
AS
(
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) =2007
GROUP BY Station_Num
--按月统计，统计8月的数据：
SELECT Station_Num,SUM(Rain_1hCur) AS Rain_1hCur 
FROM T WHERE DATEPART(MM,T.Date_time) =08
GROUP BY Station_Num
--按周统计，统计第8周的数据：
SELECT Station_Num,SUM(Rain_1hCur) AS Rain_1hCur 
FROM T WHERE DATEPART(MM,T.Date_time) =4
GROUP BY Station_Num

--楼主是这个意思么？

[唐诗三百首]

-- 按年统计
select datepart(yyyy,left(Data_time,8)) 'year',Station_Num,
sum(Rain_1hCur) sumRain_1hCur
from tab
group by datepart(yyyy,left(Data_time,8)),Station_Num

-- 按月统计
select datepart(mm,left(Data_time,8)) 'month',Station_Num,
sum(Rain_1hCur) sumRain_1hCur
from tab
group by datepart(mm,left(Data_time,8)),Station_Num

-- 按周统计
select datepart(w,left(Data_time,8)) 'week',Station_Num,
sum(Rain_1hCur) sumRain_1hCur
from tab
group by datepart(w,left(Data_time,8)),Station_Num