现在有两列值
a b
10 30
20 50
90 20
15 40
现在比较两列的平均值 哪列的平均值小 就对该列进行更新(更新到其中某一个的值达到峰值 如70 为止)不过还有个更变态的附加条件: 不许用临时变量 -_- 哪位给看看 在这先谢过了!
a b
10 30
20 50
90 20
15 40
现在比较两列的平均值 哪列的平均值小 就对该列进行更新(更新到其中某一个的值达到峰值 如70 为止)不过还有个更变态的附加条件: 不许用临时变量 -_- 哪位给看看 在这先谢过了!
set a = case when (select avg(a)from a)>(select avg(b)from a) then a
else ..... end,
b = case when (select avg(a)from a)>(select avg(b)from a) then ...
else b end
set a = case when (select avg(a)from a)>(select avg(b)from a) 0r a> 70 then a
else a + 1 end,
b = case when (select avg(a)from a)>(select avg(b)from a) and b < 70 then b+ 1
else b end
insert @t select
10 , 30 union select
20 , 50 union select
90 , 20 union select
15 , 40
update @t
set a = case when (select avg(a)from @t)>(select avg(b)from @t) or a> 70 then a
else a + 1 end,
b = case when (select avg(a)from @t)>(select avg(b)from @t) and b < 70 then b+ 1
else b endselect * from @t/*
a b
----------- -----------
11 30
16 40
21 50
90 20*/
insert @t select
10 , 30 union select
20 , 50 union select
90 , 20 union select
15 , 40
update @t
set a = case when (select avg(a)from @t)>(select avg(b)from @t) or a> 70 then a
else a + 1 end,
b = case when (select avg(a)from @t)>(select avg(b)from @t) and b < 70 then b+ 1
else b endselect * from @t/*
a b
----------- -----------
11 30
16 40
21 50
90 20*/update @t
set a = case when (select avg(a)from @t)>(select avg(b)from @t) or a> 70 then a
else a + 1 end,
b = case when (select avg(a)from @t)>(select avg(b)from @t) and b < 70 then b+ 1
else b endselect * from @t
/*a b
----------- -----------
12 30
17 40
22 50
90 20*/
a b
10 30
20 50
50 20
10 40 均值 均值
90/4 140/4a列均值小 估对其更新 更新的结果应该是
30
40
70
20
大侠的回复速度好快 再次谢谢了!
insert @t select
10 , 30 union select
20 , 50 union select
50 , 20 union select
10 , 40
update @t
set a = case when (select avg(a)from @t)>(select avg(b)from @t) or a> 70 then a
else a + (select 70 - max(a) from @t) end,
b = case when (select avg(a)from @t)>(select avg(b)from @t) and b < 70 then b+ (select 70 - max(a) from @t)
else b endselect * from @t/*
a b
----------- -----------
30 30
30 40
40 50
70 20
*/