sql server 求平均值问题

Question

现在有一张表A，有4个字段，分别为id,a,b,c
id为自增长的字段，c存储结果a的平均值，但是有前提条件b相同,2条记录的平均值
例如：
id  a   b   c
---------------
1   1   a 
2   2   a
3   3   a
4   4   a
5   4   b
6   3   b
7   2   b
8   5   c
9   6   c
10  4   c
计算结果为：
id  a   b   c
---------------
1   1   a   null
2   2   a   1.5  (1+2)/2 =1.5
3   3   a   2.5  (2+3)/2=2.5  
4   4   a   3.5  (3+4)/2=3.5
5   4   b   null
6   3   b   3.5  (4+3)/2=3.5
7   2   b   2.5   (3+2)/2=2.5
8   5   c   null
9   6   c   5.5   (6+5)/2=5.5
10  4   c   5     (6+4)/2=5
求这样的语句？

Answer 1

--> --> (Roy)生成測試數據
 
set nocount on;
declare @T table([id] int,[a] int,[b] nvarchar(1))
Insert @T
select 1,1,N'a' union all
select 2,2,N'a' union all
select 3,3,N'a' union all
select 4,4,N'a' union all
select 5,4,N'b' union all
select 6,3,N'b' union all
select 7,2,N'b' union all
select 8,5,N'c' union all
select 9,6,N'c' union all
select 10,4,N'c'
 
Select 
*,c=(select cast(avg(a*1.0) as decimal(18,2)) from @T where [b]=t.[b] and ID<=t.ID)
from 
@T t
id          a           b    c
----------- ----------- ---- ---------------------------------------
1           1           a    1.00
2           2           a    1.50
3           3           a    2.00
4           4           a    2.50
5           4           b    4.00
6           3           b    3.50
7           2           b    3.00
8           5           c    5.00
9           6           c    5.50
10          4           c    5.00

Answer 2

改改
--> --> (Roy)生成測試數據
 
set nocount on;
declare @T table([id] int,[a] int,[b] nvarchar(1))
Insert @T
select 1,1,N'a' union all
select 2,2,N'a' union all
select 3,3,N'a' union all
select 4,4,N'a' union all
select 5,4,N'b' union all
select 6,3,N'b' union all
select 7,2,N'b' union all
select 8,5,N'c' union all
select 9,6,N'c' union all
select 10,4,N'c'
 
Select 
*,c=(select cast(avg(a*1.0) as decimal(18,2)) from @T where [b]=t.[b] and ID in(select top 2 ID from @T where ID<t.ID and b=t.b order by ID desc) )
from 
@T tid          a           b    c
----------- ----------- ---- ---------------------------------------
1           1           a    NULL
2           2           a    1.00
3           3           a    1.50
4           4           a    2.50
5           4           b    NULL
6           3           b    4.00
7           2           b    3.50
8           5           c    NULL
9           6           c    5.00
10          4           c    5.50

Answer 3

--> liangCK小梁 于2008-10-03
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id INT,a INT,b VARCHAR(1),c int)
INSERT INTO #T
SELECT 1,1,'a',null UNION ALL
SELECT 2,2,'a',null UNION ALL
SELECT 3,3,'a',null UNION ALL
SELECT 4,4,'a',null UNION ALL
SELECT 5,4,'b',null UNION ALL
SELECT 6,3,'b',null UNION ALL
SELECT 7,2,'b',null UNION ALL
SELECT 8,5,'c',null UNION ALL
SELECT 9,6,'c',null UNION ALL
SELECT 10,4,'c',null--SQL查询如下:SELECT id,a,b,
       c=CAST(((SELECT TOP 1 a 
          FROM #T 
          WHERE b=t.b 
            AND id<t.id 
          ORDER BY a DESC)+a)*1.0/2 AS DECIMAL(19,2))
FROM #T AS t/*
id          a           b    c
----------- ----------- ---- ---------------------------------------
1           1           a    NULL
2           2           a    1.50
3           3           a    2.50
4           4           a    3.50
5           4           b    NULL
6           3           b    3.50
7           2           b    3.00
8           5           c    NULL
9           6           c    5.50
10          4           c    5.00(10 行受影响)*/

Answer 4

--> --> (Roy)生成測試數據
 
set nocount on;
declare @T table([id] int,[a] int,[b] nvarchar(1))
Insert @T
select 1,1,N'a' union all
select 2,2,N'a' union all
select 3,3,N'a' union all
select 4,4,N'a' union all
select 5,4,N'b' union all
select 6,3,N'b' union all
select 7,2,N'b' union all
select 8,5,N'c' union all
select 9,6,N'c' union all
select 10,4,N'c'
 
Select 
*,c=case when t2.ID<>t.ID then (select cast(avg(a*1.0) as decimal(18,2)) from @T where [b]=t.[b] 
and ID in(select top 2 ID from @T where ID<=t.ID and b=t.b order by ID desc) ) end
from 
@T t
left join
(select b,min(ID)ID from @T group by B )t2 on t.b=t2.b
id          a           b    b    ID          c
----------- ----------- ---- ---- ----------- ---------------------------------------
1           1           a    a    1           NULL
2           2           a    a    1           1.50
3           3           a    a    1           2.50
4           4           a    a    1           3.50
5           4           b    b    5           NULL
6           3           b    b    5           3.50
7           2           b    b    5           2.50
8           5           c    c    8           NULL
9           6           c    c    8           5.50
10          4           c    c    8           5.00

Answer 5

declare @T table([id] int,[a] int,[b] nvarchar(1))
Insert @T
select 1,1,N'a' union all
select 2,2,N'a' union all
select 3,3,N'a' union all
select 4,4,N'a' union all
select 5,4,N'b' union all
select 6,3,N'b' union all
select 7,2,N'b' union all
select 8,5,N'c' union all
select 9,6,N'c' union all
select 10,4,N'c'
 
Select 
    *,c=(a+(select a from @T where [b]=t.[b] and ID=t.ID-1))*1.0/2
from 
    @T t--结果
id          a           b    c                    
----------- ----------- ---- -------------------- 
1           1           a    NULL
2           2           a    1.500000
3           3           a    2.500000
4           4           a    3.500000
5           4           b    NULL
6           3           b    3.500000
7           2           b    2.500000
8           5           c    NULL
9           6           c    5.500000
10          4           c    5.000000（所影响的行数为 10 行）

Answer 6

--> --> (Roy)生成測試數據
 
set nocount on;
declare @T table([id] int,[a] int,[b] nvarchar(1))
Insert @T
select 1,1,N'a' union all
select 2,2,N'a' union all
select 3,3,N'a' union all
select 4,4,N'a' union all
select 5,4,N'b' union all
select 6,3,N'b' union all
select 7,2,N'b' union all
select 8,5,N'c' union all
select 9,6,N'c' union all
select 10,4,N'c'
 
Select 
t.*,c=case when t2.ID<>t.ID then (select cast(avg(a*1.0) as decimal(18,2)) from @T where [b]=t.[b] 
and ID in(select top 2 ID from @T where ID<=t.ID and b=t.b order by ID desc) ) end
from 
@T t
left join
(select b,min(ID)ID from @T group by B )t2 on t.b=t2.b--05可用
;with c
as
(select *,row=row_number()over(Partition by b order by ID asc) from @T )
select 
c.ID,c.a,c.b,c=cast((c.a+t.a)*1.0 /2 as decimal(18,2))
from 
c 
left join
c t on c.b=t.b and c.row=t.row+1
order by c.ID/*
id          a           b    c
----------- ----------- ---- ---------------------------------------
1           1           a    NULL
2           2           a    1.50
3           3           a    2.50
4           4           a    3.50
5           4           b    NULL
6           3           b    3.50
7           2           b    2.50
8           5           c    NULL
9           6           c    5.50
10          4           c    5.00ID          a           b    c
----------- ----------- ---- ---------------------------------------
1           1           a    NULL
2           2           a    1.50
3           3           a    2.50
4           4           a    3.50
5           4           b    NULL
6           3           b    3.50
7           2           b    2.50
8           5           c    NULL
9           6           c    5.50
10          4           c    5.00
*/