根据年龄和身高分类显示姓名和总成绩
select name,sum(sorce)
from student
where age =20 and hight>=160
age =21 and hight>=165
age =22 and hight>=170
age =23 and hight>=175我用union all 分开实现的
select name,sum(sorce)
from student
where age =20 and hight>=160
union all
select name,sum(sorce)
from student
where age =21 and hight>=165
select name,sum(sorce)
from student
where age =20 and hight>=160
age =21 and hight>=165
age =22 and hight>=170
age =23 and hight>=175我用union all 分开实现的
select name,sum(sorce)
from student
where age =20 and hight>=160
union all
select name,sum(sorce)
from student
where age =21 and hight>=165
解决方案 »
- oracle dblink 的问题
- 这个SQL如何优化?
- ORA-01445: cannot select ROWID from, or sample, a join view without a key-preser
- 求救:oracle10g 导入dmp文件问题
- 树枝到树根所经过的路径
- oracle存储过程的参数游标应该怎样来赋值??
- 数据库联合查询问题(很急)
- 请问怎样向clob类型的字段里插入数据,在线等!
- Oracle中触发器与外部程序通信的问题!
- 在Delphi中使用Oracle存储过程是否必须用procedure控件?不能在query中执行?
- oracle奇怪问题
- 请教一个拼动态sql的问题
(select deptno, ename from emp)
select * from my_test_block
union all
select * from my_test_block
这样,查询一次,然后在查询结果中进行再查询,效率更高
select name,sum(sorce)
from student
where
case age when 20 then hight>=160
when 21 then hight>=165
when 22 then hight>=170
when 23 then hight>=175
group by name
/
分组可以用 group by ... having .....
或多个查询union all
(select name, case when (age=20 and hight>=160) or
(age=21 and hight>=165) or
(age=22 and hight>=170) or
(age=23 and hight>=175) then sorce end sorce,from student)
where score is not null
group by age
这样应该可以满足楼主的要求把
不过根据年龄分组,姓名就得从结果里去掉了
select age,sum(sorce)
(select name, age,
case when (age=20 and hight>=160) or
(age=21 and hight>=165) or
(age=22 and hight>=170) or
(age=23 and hight>=175) then sorce end sorce,from student)
where score is not null
group by age
select name,sum(sorce)
from student
where (age =20 and hight>=160 )
or
(age =21 and hight>=165 )
or
(age =22 and hight>=170 )
or
(age =23 and hight>=175 )
group by name