急急急..关于查询统计问题

Question

id            rq          value     
1          2009-04-01      1
2          2009-04-01      2
3          2009-04-01      3
5          2009-04-01      4
6          2009-04-01      5
7          2009-04-01      6
8          2009-04-01      7
9          2009-04-01      8
1          2009-04-02      2
2          2009-04-02      3
3          2009-04-02      4
5          2009-04-02      5
6          2009-04-02      6
7          2009-04-02      6
8          2009-04-02      7
9          2009-04-02      8
1          2009-04-03      1
2          2009-04-03      2
3          2009-04-03      3
5          2009-04-03      4
6          2009-04-03      5
7          2009-04-03      6
8          2009-04-03      7
9          2009-04-03      8
1          2009-04-04      7
2          2009-04-04      2
3          2009-04-04      3
5          2009-04-04      4
6          2009-04-04      5
7          2009-04-04      6
8          2009-04-04      7
9          2009-04-04      8
测试数据如上我现在选择时间为2009-04-01和 2009-04-04 我要计算这个时间段所有ID号的平均值 公式如下=（2009-04-01的VALUE - 2009-04-04的VAKUE）/ 2009-04-04的VAKUE
例如计算ID 为1 的平均值 应该是 （1-7）/ 7

Answer 1

select rs=(select a.id,(a.value-b.value)/a.value as 'avgValue' from (select * from Table where time='2009-04-01') a,(select * from Table where time='2009-04-04') b where a.id=b.id) 

Answer 2

不是AVG.是要用这个公式算
打个比方就是下面这个样子..id            rq          value       newvalue
1          2009-04-01      1          (1-7)/7
2          2009-04-01      2          (2-2)/2 
3          2009-04-01      3          (3-3)/3
5          2009-04-01      4            ..
6          2009-04-01      5            ..
7          2009-04-01      6 
8          2009-04-01      7 
9          2009-04-01      8 
1          2009-04-02      2 
2          2009-04-02      3 
3          2009-04-02      4 
5          2009-04-02      5 
6          2009-04-02      6 
7          2009-04-02      6 
8          2009-04-02      7 
9          2009-04-02      8 
1          2009-04-03      1 
2          2009-04-03      2 
3          2009-04-03      3 
5          2009-04-03      4 
6          2009-04-03      5 
7          2009-04-03      6 
8          2009-04-03      7 
9          2009-04-03      8 
1          2009-04-04      7 
2          2009-04-04      2 
3          2009-04-04      3 
5          2009-04-04      4 
6          2009-04-04      5 
7          2009-04-04      6 
8          2009-04-04      7 
9          2009-04-04      8 

Answer 3

SELECT ID, CAST(value AS float)/(SELECT value FROM [TableName] B WHERE A.ID=B.ID AND B.rq IN(SELECT max(rq) FROM [TableName] C WHERE C.ID=B.ID))-1
FROM [TableName] A
WHERE rq IN(SELECT Min(rq) FROM [TableName] B WHERE A.ID=B.ID)结果：
ID                                                                
----------- ----------------------------------------------------- 
1           -0.85714285714285721
2           0.0
3           0.0
5           0.0
6           0.0
7           0.0
8           0.0
9           0.0

Answer 4

SELECT ID, CAST(value AS float)/(
    SELECT value FROM tablename B 
    WHERE A.ID=B.ID AND B.rq IN(SELECT max(rq) FROM tablename C WHERE C.ID=B.ID)
  )-1
FROM tablename A
WHERE rq IN(SELECT Min(rq) FROM tablename B WHERE A.ID=B.ID)结果:
ID                                                                
----------- ----------------------------------------------------- 
1           -0.85714285714285721
2           0.0
3           0.0
5           0.0
6           0.0
7           0.0
8           0.0
9           0.0

Answer 5

--------对每一记录计算相对于最后一天的平均值 ---------------------------------
SELECT ID, rq, value, CAST(value AS float)/(
    SELECT value FROM #temp_1 B 
    WHERE A.ID=B.ID AND B.rq IN(SELECT max(rq) FROM #temp_1 C WHERE C.ID=B.ID)
  )-1 AS newValue
FROM #temp_1 A结果：
ID          rq                       value       newValue                                              
----------- ------------------------ ----------- ------------------------------------- 
1           2009-04-01 00:00:00.000  1           -0.85714285714285721
2           2009-04-01 00:00:00.000  2           0.0
3           2009-04-01 00:00:00.000  3           0.0
5           2009-04-01 00:00:00.000  4           0.0
6           2009-04-01 00:00:00.000  5           0.0
7           2009-04-01 00:00:00.000  6           0.0
8           2009-04-01 00:00:00.000  7           0.0
9           2009-04-01 00:00:00.000  8           0.0
1           2009-04-02 00:00:00.000  2           -0.7142857142857143
2           2009-04-02 00:00:00.000  3           0.5
3           2009-04-02 00:00:00.000  4           0.33333333333333326
5           2009-04-02 00:00:00.000  5           0.25
6           2009-04-02 00:00:00.000  6           0.19999999999999996
7           2009-04-02 00:00:00.000  6           0.0
8           2009-04-02 00:00:00.000  7           0.0
9           2009-04-02 00:00:00.000  8           0.0
1           2009-04-03 00:00:00.000  1           -0.85714285714285721
2           2009-04-03 00:00:00.000  2           0.0
3           2009-04-03 00:00:00.000  3           0.0
5           2009-04-03 00:00:00.000  4           0.0
6           2009-04-03 00:00:00.000  5           0.0
7           2009-04-03 00:00:00.000  6           0.0
8           2009-04-03 00:00:00.000  7           0.0
9           2009-04-03 00:00:00.000  8           0.0
1           2009-04-04 00:00:00.000  7           0.0
2           2009-04-04 00:00:00.000  2           0.0
3           2009-04-04 00:00:00.000  3           0.0
5           2009-04-04 00:00:00.000  4           0.0
6           2009-04-04 00:00:00.000  5           0.0
7           2009-04-04 00:00:00.000  6           0.0
8           2009-04-04 00:00:00.000  7           0.0
9           2009-04-04 00:00:00.000  8           0.0

Answer 6

select (a.value-b.value)/b.value
from
(select * from myTable where rq='2009-04-01') a,
(select * from myTable where rq='2009-04-04') b
where a.id=b.id