前提 oracle数据库
a表字段name,age这两列,name不能为空,age可以为空;
示例数据
name | age |
---|---|
张三 | |
张三 | 18 |
李四 |
要求:筛选出name不同的数据,如果name相同选出年龄最大的那一条;
sql1:
select * from a,(select name,max(age) from a group by name)b where
a.name=b.name and (case when b.age is null then 1
else a.age end)=(case when b.age is null then 1
else b.age end);
sql2:
select * from a,(select name,max(age) from a group by name)b where
a.name=b.name and nvl2(b.age,a.age,1)=nvl2(b.age,b.age,1);