select disintct name,date from t where t.date=(select max(date) from t)
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
With T As( 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;
select disintct t1.name,t1.date from t t1,(select name max(date) max_dt from t group by name) t2 where t1.name=t2.name and t1.date=t2.max_dt
( 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;