数据库如下
ID 部门ID 监测开始时间 监测值
1 101 2011-03-22 10:22:23.891 23
2 102 2011-03-22 10:25:23.891 24
3 102 2011-03-22 11:22:23.891 20
4 101 2011-03-22 11:56:23.891 23
5 103 2011-03-22 12:22:23.891 27
6 103 2011-03-23 10:32:33.891 28
7 102 2011-03-23 10:52:23.891 22
8 103 2011-03-23 11:12:23.891 25
9 102 2011-03-23 11:28:23.891 20
10 101 2011-03-23 12:56:23.891 21
11 101 2011-03-23 15:33:23.891 24
……要求每个部门一天可能上报多天数据,写一条语句或是存储过程等查询一个部门所有时间的平均值
例如 部门1的数据时间段 2011-03-22~2012-04-11 那么就要求求出部门1在这段时间内的平均值
但是求平均值的要求时每天的最后一条数据才参与求平均值小弟恳求各位大侠给解~
ID 部门ID 监测开始时间 监测值
1 101 2011-03-22 10:22:23.891 23
2 102 2011-03-22 10:25:23.891 24
3 102 2011-03-22 11:22:23.891 20
4 101 2011-03-22 11:56:23.891 23
5 103 2011-03-22 12:22:23.891 27
6 103 2011-03-23 10:32:33.891 28
7 102 2011-03-23 10:52:23.891 22
8 103 2011-03-23 11:12:23.891 25
9 102 2011-03-23 11:28:23.891 20
10 101 2011-03-23 12:56:23.891 21
11 101 2011-03-23 15:33:23.891 24
……要求每个部门一天可能上报多天数据,写一条语句或是存储过程等查询一个部门所有时间的平均值
例如 部门1的数据时间段 2011-03-22~2012-04-11 那么就要求求出部门1在这段时间内的平均值
但是求平均值的要求时每天的最后一条数据才参与求平均值小弟恳求各位大侠给解~
select deptid,avg(val) val
from tb t
where date between @start and @end
and not exists (select 1 from tb where date between @start and @end
and deptid = deptid and datediff(dd,date,t.date)=0 and date > t.date)
group by deptid
@end这不是起始日期和截止日期么。
ID 部门ID 监测开始时间 监测值
1 101 2011-03-22 10:22:23.891 23
2 102 2011-03-22 10:25:23.891 24
3 102 2011-03-22 11:22:23.891 20
4 101 2011-03-22 11:56:23.891 23
5 103 2011-03-22 12:22:23.891 27
6 103 2011-03-23 10:32:33.891 28
7 102 2011-03-23 10:52:23.891 22
8 103 2011-03-23 11:12:23.891 25
9 102 2011-03-23 11:28:23.891 20
10 101 2011-03-23 12:56:23.891 21
11 101 2011-03-23 15:33:23.891 24我要SQL语句查出来的是 比如是部门1:
4 101 2011-03-22 11:56:23.891 23
11 101 2011-03-23 15:33:23.891 24这两条数据的监测值的平均值意思也就是说没天的最后一条数据去求平均值比如是部门2:
3 102 2011-03-22 11:22:23.891 20
9 102 2011-03-23 11:28:23.891 20这两条数据的监测值的平均值以此类推
……
select deptid,avg(val) val
from tb t
where date between @start and @end
and not exists (select 1 from tb where date between @start and @end
and deptid = deptid and datediff(dd,date,t.date)=0 and date > t.date)
group by deptid
得到具体的行数据
select *
from tb t
where date between @start and @end
and not exists (select 1 from tb where date between @start and @end
and deptid = deptid and datediff(dd,date,t.date)=0 and date > t.date)
and deptid = 1