序列号 姓名 类别 时间
1 小东 1 2012-05-15 13:57:47
2 小红 2 2012-05-15 13:57:44
3 小张 2 2012-05-15 13:57:45
4 小明 1 2012-05-15 13:57:46
5 小娜 3 2012-05-15 13:57:48
只有类别是2时候过滤掉旧的数据,显示如下
1 小东 1 2012-05-15 13:57:47
3 小张 2 2012-05-15 13:57:45
4 小明 1 2012-05-15 13:57:46
5 小娜 3 2012-05-15 13:57:48
1 小东 1 2012-05-15 13:57:47
2 小红 2 2012-05-15 13:57:44
3 小张 2 2012-05-15 13:57:45
4 小明 1 2012-05-15 13:57:46
5 小娜 3 2012-05-15 13:57:48
只有类别是2时候过滤掉旧的数据,显示如下
1 小东 1 2012-05-15 13:57:47
3 小张 2 2012-05-15 13:57:45
4 小明 1 2012-05-15 13:57:46
5 小娜 3 2012-05-15 13:57:48
select t1.*
from t as t1
where exists(
select *
from t as t2
where t1.姓名=t2.姓名
and (
(t1.类别=2 and t1.序列号=max(t2.序列号))
or t1.类别<>2
)
)
select t1.*
from t as t1
where t1.类别<>2 or exists(
select *
from t as t2
where t1.姓名=t2.姓名
and t1.类别=2
having(t1.序列号=max(t2.序列号))
)
WITH a AS (
SELECT 1 xh,'小东' xm ,1 lb ,to_date('2012-05-15 13:57:47','yyyy-mm-dd hh24:mi:ss') rq from dual UNION
SELECT 2 ,'小红' ,2 , to_date('2012-05-15 13:57:44','yyyy-mm-dd hh24:mi:ss') from dual UNION
SELECT 3 ,'小张' ,2 , to_date('2012-05-15 13:57:45','yyyy-mm-dd hh24:mi:ss') from dual UNION
SELECT 4 ,'小明' ,1 , to_date('2012-05-15 13:57:46','yyyy-mm-dd hh24:mi:ss') from dual UNION
SELECT 5 ,'小娜' ,3 , to_date('2012-05-15 13:57:48','yyyy-mm-dd hh24:mi:ss') from dual
)
SELECT * FROM a WHERE lb<>2
UNION ALL
SELECT xh,xm,lb,rq FROM a WHERE lb=2 AND rq IN (SELECT MAX(rq) FROM a WHERE lb=2)
from xxx a1
where a1.类别 <> 2
or a1.时间 =
(select max(a2.时间) from xxx a2 where 类别 = 2) 没有用到having.select * from xxx t where t.类别<>2 or exists
(
select *
from xxx t2
where t.名称 = t2.名称
or t2.类别 = 2 having(t.时间=max(t2.时间))
); 用一下having
(select 1 xlh,'小东' xm,'1' lb,to_date('2012-05-15 13:57:47','yyyy-mm-dd hh24:mi:ss') sj from dual
union all
select 2 xlh,'小红' xm,'2' lb,to_date('2012-05-15 13:57:44','yyyy-mm-dd hh24:mi:ss') sj from dual
union all
select 3 xlh,'小张' xm,'2' lb,to_date('2012-05-15 13:57:45','yyyy-mm-dd hh24:mi:ss') sj from dual
union all
select 4 xlh,'小明' xm,'1' lb,to_date('2012-05-15 13:57:46','yyyy-mm-dd hh24:mi:ss') sj from dual
union all
select 5 xlh,'小娜' xm,'3' lb,to_date('2012-05-15 13:57:48','yyyy-mm-dd hh24:mi:ss') sj from dual
)
select xlh,xm,lb,sj from (select xlh,xm,lb,sj,row_number() over (partition by lb order by sj desc) rn from t)
where (lb<>2) or (rn=1 and lb=2)