解决方案 »
- oracle分区表导出的问题
- 如何把新增字段添加到指定位置?
- 自建立DML日志表
- PL/SQL能否设置相同字符高亮显示
- oracle805 & win2000server下导入数据,错误提示sql*loader524急急急急急急急
- oracle9i安装成功后,运行总是提示“没有监听器”?
- shared_pool_size过大会对数据库性能产生影响吗?
- 在oracle 中创建用户不能达到预期的效果 在线等待(现场散分)
- 请问在ORACLE中怎么用identity建表中字段?
- 请问一下,为什么我在虚拟机中安装oracle11g的时候,界面上的下一步,取消按钮一直在闪烁?
- 江湖救急,oracle text大字段如何右边补齐标签?
- 唯一键不能产生唯一索引,请大神帮下忙
( select name,date
cnt - cnt_no as cnt_yes,
cnt_no
from ( select name,date,
count(1) as cnt,
sum(case when result <= 60 then 1 else 0) as cnt_no
from t,
( select name, max(date) from t group by name ) t1
where t.name = t.name and t.date = t1.date
group by name,date ))
select name,date,'合格' as flag from result where cnt_yes > 0
union all
select name,date,'不合格' as flag from result where cnt_no > 0
select '张三' as name,41 as result,to_date('2013-1-1','YYYY-MM-DD')as date_time,'会计基础'as course_name from dual union all
select '张三' as name,31 as result,to_date('2013-1-1','YYYY-MM-DD')as date_time,'财经法规与会计职业道德'as course_name from dual union all
select '张三' as name,60 as result,to_date('2013-1-2','YYYY-MM-DD')as date_time,'财经法规与会计职业道德'as course_name from dual union all
select '张三' as name,77 as result,to_date('2013-1-2','YYYY-MM-DD')as date_time,'会计基础'as course_name from dual union all
select '李四' as name,55 as result,to_date('2013-1-1','YYYY-MM-DD')as date_time,'会计基础'as course_name from dual union all
select '李四' as name,55 as result,to_date('2013-1-1','YYYY-MM-DD')as date_time,'财经法规与会计职业道德'as course_name from dual union all
select '李四' as name,99 as result,to_date('2013-1-2','YYYY-MM-DD')as date_time,'财经法规与会计职业道德'as course_name from dual union all
select '李四' as name,77 as result,to_date('2013-1-2','YYYY-MM-DD')as date_time,'会计基础'as course_name from dual)
Select a.Name,
a.Date_Time,
(Case
When Result >= 60 Then
'合格'
Else
'不合格'
End) As 是否合格
From (Select Name,
Date_Time,
Min(Result) Keep(Dense_Rank First Order By Date_Time Desc) Over(Partition By Name) Result,
Row_Number() Over(Partition By Name Order By Date_Time Desc) Num
From t) a
Where a.Result >= 60
And a.Num = 1
Order By a.name;
select '张三' as name,41 as result,to_date('2013-1-1','YYYY-MM-DD')as date_time,'会计基础'as course_name from dual union all
select '张三' as name,31 as result,to_date('2013-1-1','YYYY-MM-DD')as date_time,'财经法规与会计职业道德'as course_name from dual union all
select '张三' as name,60 as result,to_date('2013-1-2','YYYY-MM-DD')as date_time,'财经法规与会计职业道德'as course_name from dual union all
select '张三' as name,77 as result,to_date('2013-1-2','YYYY-MM-DD')as date_time,'会计基础'as course_name from dual union all
select '李四' as name,55 as result,to_date('2013-1-1','YYYY-MM-DD')as date_time,'会计基础'as course_name from dual union all
select '李四' as name,55 as result,to_date('2013-1-1','YYYY-MM-DD')as date_time,'财经法规与会计职业道德'as course_name from dual union all
select '李四' as name,99 as result,to_date('2013-1-2','YYYY-MM-DD')as date_time,'财经法规与会计职业道德'as course_name from dual union all
select '李四' as name,77 as result,to_date('2013-1-2','YYYY-MM-DD')as date_time,'会计基础'as course_name from dual)
Select a.Name,
a.Date_Time,
(Case
When Result >= 60 Then
'合格'
Else
'不合格'
End) As 是否合格
From (Select Name,
Date_Time,
Min(Result) Keep(Dense_Rank First Order By Date_Time Desc) Over(Partition By Name) Result,
Row_Number() Over(Partition By Name Order By Date_Time Desc) Num
From t) a
Where a.Num = 1
Order By a.name;