update tablename set isok=case when a.score1/a.score2>0.5 then 'true' else 'false' end from (select no,sum(score1)*1.0 score1,sum(score2)*1.0 score2 from tablenanme group by no) a where a.no=tablename.no
或: update 表 set IsOk=1 from 表 a inner join ( select NO,Score=sum(Score1)/sum(Score2) from 表 grou by No ) b on a.No=b.No where b.Score>0.5
如果你的表中score1和score2是整型的话,用下面的更新语句:update 表 set IsOk=1 from 表 a where (select sum(Score1+0.0)/sum(Score2) from 表 where No=a.No)>0.5或: update 表 set IsOk=1 from 表 a inner join ( select NO,Score=sum(Score1+0.0)/sum(Score2) from 表 grou by No ) b on a.No=b.No where b.Score>0.5
如果IsOk是字符型的话,用: update 表 set IsOk='True' from 表 a where (select sum(Score1+0.0)/sum(Score2) from 表 where No=a.No)>0.5或: update 表 set IsOk='True' from 表 a inner join ( select NO,Score=sum(Score1+0.0)/sum(Score2) from 表 grou by No ) b on a.No=b.No where b.Score>0.5
from (select no,sum(score1)*1.0 score1,sum(score2)*1.0 score2 from tablenanme group by no) a where a.no=tablename.no
update 表 set IsOk=1
from 表 a inner join (
select NO,Score=sum(Score1)/sum(Score2) from 表 grou by No
) b on a.No=b.No
where b.Score>0.5
from 表 a
where (select sum(Score1+0.0)/sum(Score2) from 表 where No=a.No)>0.5或:
update 表 set IsOk=1
from 表 a inner join (
select NO,Score=sum(Score1+0.0)/sum(Score2) from 表 grou by No
) b on a.No=b.No
where b.Score>0.5
update 表 set IsOk='True'
from 表 a
where (select sum(Score1+0.0)/sum(Score2) from 表 where No=a.No)>0.5或:
update 表 set IsOk='True'
from 表 a inner join (
select NO,Score=sum(Score1+0.0)/sum(Score2) from 表 grou by No
) b on a.No=b.No
where b.Score>0.5
1、使用分组和不分组两种方法哪种效率高?
2、如果是整数为什么要 +0.0?
应为要转换成浮点数。
Select 5/10
select 5.0/10