表t_demo_EqDate如下(只列出了两个电表的部分度数如下)
Id EqId EqValue EqTime
91 1 92.712 2008-3-31
92 1 52.507 2008-4-1
93 1 63.6 2008-4-2
94 1 38.347 2008-4-3
95 1 68.051 2008-4-4
96 1 68.975 2008-4-5
97 1 4.905 2008-4-6
98 1 41.767 2008-4-7
99 1 48.011 2008-4-8
100 1 90.509 2008-4-9
101 2 83.558 2008-1-1
102 2 27.526 2008-1-2
103 2 72.113 2008-1-3
104 2 95.702 2008-1-4
105 2 74.275 2008-1-5
106 2 20.829 2008-1-6
107 2 40.767 2008-1-7
108 2 56.027 2008-1-8
109 2 83.615 2008-1-9
110 2 69.288 2008-1-10
111 2 38.632 2008-1-11问题:现在要统计每个电表的每周的总电表值(也就是统计每个电表在每一周的度数总和) (急,回答正确都有分)
if object_id('[t_demo_EqDate]') is not null drop table [t_demo_EqDate]
go
create table [t_demo_EqDate]([Id] int,[EqId] int,[EqValue] numeric(5,3),[EqTime] datetime)
insert [t_demo_EqDate]
select 91,1,92.712,'2008-3-31' union all
select 92,1,52.507,'2008-4-1' union all
select 93,1,63.6,'2008-4-2' union all
select 94,1,38.347,'2008-4-3' union all
select 95,1,68.051,'2008-4-4' union all
select 96,1,68.975,'2008-4-5' union all
select 97,1,4.905,'2008-4-6' union all
select 98,1,41.767,'2008-4-7' union all
select 99,1,48.011,'2008-4-8' union all
select 100,1,90.509,'2008-4-9' union all
select 101,2,83.558,'2008-1-1' union all
select 102,2,27.526,'2008-1-2' union all
select 103,2,72.113,'2008-1-3' union all
select 104,2,95.702,'2008-1-4' union all
select 105,2,74.275,'2008-1-5' union all
select 106,2,20.829,'2008-1-6' union all
select 107,2,40.767,'2008-1-7' union all
select 108,2,56.027,'2008-1-8' union all
select 109,2,83.615,'2008-1-9' union all
select 110,2,69.288,'2008-1-10' union all
select 111,2,38.632,'2008-1-11'
---查询---select
cast(year(EqTime) as varchar(10))+'年'+right('00'+cast(datepart(wk,EqTime) as varchar(2)),2)+'周' as 周,
sum(EqValue) as EqValue
from [t_demo_EqDate]
group by cast(year(EqTime) as varchar(10))+'年'+right('00'+cast(datepart(wk,EqTime) as varchar(2)),2)+'周'
---结果---
周 EqValue
------------------ ---------------------------------------
2008年01周 353.174
2008年02周 309.158
2008年14周 384.192
2008年15周 185.192(4 行受影响)
select eqid,sum(eqvalue) Sumeqvalue,datepart(week,eqtime) Weekeqtime from testtable
group by eqid,datepart(week,eqtime)eqid Sumeqvalue Weekeqtime
----------- ----------------------------------------------------- -----------
2 353.17399999999998 1
2 270.52600000000001 2
1 384.19200000000001 14
1 185.19200000000001 15