日期:2014-05-17 浏览次数:20416 次
--测试tb表
create table tb(
CIFSEQ integer,
EDITIONPRE integer,
EDITIONAFTER integer,
CHANGEDATE date
);
--测试数据
iinsert into tb values(13,1,0,to_date('2011-5-29 16:45:21', 'yyyy-mm-dd hh24:mi:ss'));
insert into tb values(13,0,1,to_date('2011-5-28 9:17:15', 'yyyy-mm-dd hh24:mi:ss'));
insert into tb values(11,1,0,to_date('2011-5-28 10:11:53', 'yyyy-mm-dd hh24:mi:ss'));
insert into tb values(11,0,1,to_date('2011-5-29 13:22:34', 'yyyy-mm-dd hh24:mi:ss'));
insert into tb values(15,0,1,to_date('2011-5-01 03:42:08', 'yyyy-mm-dd hh24:mi:ss'));
--第一种方法
select * from tb a where a.CHANGEDATE=(select max(CHANGEDATE) from tb b where a.CIFSEQ= b.CIFSEQ) order by a.cifseq;
--第二种方法
SELECT a.* FROM tb a
INNER JOIN
(
SELECT CIFSEQ,max(CHANGEDATE) as CHANGEDATE FROM tb
GROUP BY CIFSEQ
) b ON a.CIFSEQ = b.CIFSEQ AND a.CHANGEDATE = b.CHANGEDATE order by a.cifseq;
--第三种方法
select * from tb a where not exists(select 1 from tb b where a.CIFSEQ=b.CIFSEQ and b.CHANGEDATE>a.CHANGEDATE) order by a.cifseq;
select * from tb a where not exists(select 1 from tb b where a.CIFSEQ=b.CIFSEQ and b.CHANGEDATE>a.CHANGEDATE)
--第一种方法
select * from tb a where a.CHANGEDATE=(select max(CHANGEDATE) from tb b where a.CIFSEQ= b.CIFSEQ) order by a.cifseq;
--第二种方法
SELECT a.* FROM tb a
INNER JOIN
(
SELECT CIFSEQ,max(CHANGEDATE) as CHANGEDATE FROM tb
GROUP BY CIFSEQ
) b ON a.CIFSEQ = b.CIFSEQ AND a.CHANGEDATE = b.CHANGEDATE order by a.cifseq;
--第三种方法
select * from tb a where not exists(select 1 from tb b where a.CIFSEQ=b.CIFSEQ and b.CHANGEDATE>a.CHANGEDATE) order by a.cifseq;