计算totalsum的值,并update该值
表如下:
studentid chinese math english totalsum
0001 80 70 60
0002 40 50 80
0001 20 100 40
0003 10 100 30
0001 50 70 80
....
统计0001和0002的所有记录三门学科成绩之和如果大于500
则totalsum=(chinese math english)*0.5
如果小于500
则totalsum=(chinese math english)*0.2
表如下:
studentid chinese math english totalsum
0001 80 70 60
0002 40 50 80
0001 20 100 40
0003 10 100 30
0001 50 70 80
....
统计0001和0002的所有记录三门学科成绩之和如果大于500
则totalsum=(chinese math english)*0.5
如果小于500
则totalsum=(chinese math english)*0.2
解决方案 »
- 这个while语句是啥意思?
- 一个项目中碰到的问题,麻烦点进看
- 求实现每隔5秒运行此程序,捕获是否有(a.exe 暂用此名称代替)的窗口弹出,聚焦此窗口,发回车命令!
- 谁有用过Indy的IdTCPServer控件?是否每个接入的客户端都会自动产生一个线程?
- 制作WEB中使用的OCX时,如何获得在URL中传输的外部参数
- 如何自动登陆到服务器
- http://www.csdn.net/expert/topic/468/468185.xml?temp=.3393518这样的贴子为什么打不开!
- 千两狂死郎兄,请贴个人资料,急用啊!!
- 请再问彭哥兄,能否教一招最简单的PassWord方法
- Delphi5 编译生成的.dsm文件的疑问?
- 在函数中定义一个引用了TADOQuery, 函数执行结束, TADOQuery是否自动释放 ?
- 新手提问!与spl server连接失败~
Set totalsum = B.totalsum
From 表 A
Left JOIN
(Select CASE WHEN chinese+math+english>500 then (chinese+math+english)*0.5 ELSE (chinese+math+english)*0.2 End totalsum,studentid
From 表 where studentid IN ('0001','0002')) B
ON A.studentid=B.studentid
The column prefix 'B' does not match with a table name or alias name used in the query.
SET totalsum =
CASE WHEN (ISNULL( chinese,0) + ISNULL(math,0) + ISNULL(english,0))>500
THEN (ISNULL( chinese,0) + ISNULL(math,0) + ISNULL(english,0))*0.5
ELSE (ISNULL( chinese,0) + ISNULL(math,0) + ISNULL(english,0))*0.2 End
WHERE studentid in ('0001','0002')
case when studentid
in (select studentid from table_a group by studentid having sum(chinese+math+english)>=500)
then (chinese+math+english)*0.5
else
(chinese+math+english)*0.2
end
where studentid in ('0001,'0002')
要改成iif,但我改成:
update test1 set totalsum=
iif (studentid in (select studentid from test1 group by studentid having
sum (chinese+math+english)>=500),(chinese+math+english)*0.5,0)
iif (studentid in (select studentid from test1 group by studentid having
sum(chinese+math+english)<500),(chinese+math+english)*0.2)
where studentid in ('0001','0002')为何还是不行?
update table_a set totalsum=(chinese+math+english)*0.5
where studentid
in (select studentid from table_a group by studentid having sum(chinese+math+english)>=500)
and studentid in ('0001,'0002')