name result date course_name
张三 41 2013-1-1 会计基础
张三 31 2013-1-1 财经法规与会计职业道德
张三 60 2013-1-2 财经法规与会计职业道德
张三 77 2013-1-2 会计基础
李四 55 2013-1-1 会计基础
李四 55 2013-1-1 财经法规与会计职业道德
李四 99 2013-1-2 财经法规与会计职业道德
李四 77 2013-1-2 会计基础
数据库结构是这样的 需求是 查询出最近一次考试合格或不合格的人，要去重
结果要这样要当天考试所有成绩大于60才算合格
name date
张三 2013-1-2
李四 2013-1-2
------解决思路----------------------
with result as
( 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.Num = 1
Order By a.name;