SQL

select * from (
select s1.* ,s2.snap_file as oldpic  from page_snapshot s1

left join page_snapshot s2
on s1.page_issue_id = s2.page_issue_id and

s2.snap_time<s1.snap_time
where s1.page_issue_id = 413
order by s1.snap_time desc, s2.snap_time desc
) where ROWNUM <= 1


1.
oldpic  写成'oldpic' 唔得

2.
s2.* 唔得，有同名

----------------------------------------------------

----------------------
第一
-----------------------------
select s1.page_issue_id,Max(s1.snap_time) as top1
from page_snapshot s1
group by s1.page_issue_id


-------------
第二
-----------------------------
select s1.page_issue_id,Max(s1.snap_time) as top2 from page_snapshot s1
where s1.snap_time < (select max(s2.snap_time) from page_snapshot s2
where s1.page_issue_id=s2.page_issue_id)  
group by s1.page_issue_id

----------------------------
ok的
-----------------------------
select ra1.page_issue_id,ra1.top1,ra2.top2,
ps1.snap_file as newfile,ps2.snap_file as oldfile,
ps1.make_file,ps1.make_step,ps1.make_message from
(
select s1.page_issue_id,Max(s1.snap_time) as top1
from page_snapshot s1
group by s1.page_issue_id
) ra1
left join
(
select s1.page_issue_id,Max(s1.snap_time) as top2 from
page_snapshot s1
where s1.snap_time < (select max(s2.snap_time) from
page_snapshot s2
where s1.page_issue_id=s2.page_issue_id)  
group by s1.page_issue_id
) ra2
on ra1.page_issue_id= ra2.page_issue_id
left join page_snapshot ps1
on ra1.page_issue_id= ps1.page_issue_id
and ra1.top1 = ps1.snap_time
left join page_snapshot ps2
on ra2.page_issue_id = ps2.page_issue_id
and ra2.top2 = ps2.snap_time


----------------------
OK OK
------------------------------
select ra1.page_issue_id,ra1.top1,ra2.top2,
ps1.snap_file as newfile,ps2.snap_file as oldfile,
ps1.make_file,ps1.make_step,ps1.make_message from
(
select s1.page_issue_id,Max(s1.snap_time) as top1
from page_snapshot s1
group by s1.page_issue_id
) ra1
left join
(
select s1.page_issue_id,Max(s1.snap_time) as top2 from
page_snapshot s1
where s1.snap_time < (select max(s2.snap_time) from
page_snapshot s2
where s1.page_issue_id=s2.page_issue_id)  
group by s1.page_issue_id
) ra2
on ra1.page_issue_id= ra2.page_issue_id
inner join page_snapshot ps1
on ra1.page_issue_id= ps1.page_issue_id
and ps1.make_step=0
and ra1.top1 = ps1.snap_time
inner join page_snapshot ps2
on ra2.page_issue_id = ps2.page_issue_id
and ra2.top2 = ps2.snap_time

----------------------
OK OK
------------------------------
select ra1.page_issue_id,ra1.top1,ra2.top2,
ps1.snap_file as newfile,ps2.snap_file as oldfile,
ps1.make_file,ps1.make_step,ps1.make_message from
(
select s1.page_issue_id,Max(s1.snap_time) as top1
from page_snapshot s1
group by s1.page_issue_id
) ra1
left join
(
select s1.page_issue_id,Max(s1.snap_time) as top2 from
page_snapshot s1
where s1.snap_time < (select max(s2.snap_time) from
page_snapshot s2
where s1.page_issue_id=s2.page_issue_id)  
group by s1.page_issue_id
) ra2
on ra1.page_issue_id= ra2.page_issue_id
inner join page_snapshot ps1
on ra1.page_issue_id= ps1.page_issue_id
//and ps1.make_step=0
and ra1.top1 = ps1.snap_time
inner join page_snapshot ps2
on ra2.page_issue_id = ps2.page_issue_id
and ra2.top2 = ps2.snap_time
where ps1.make_step=0

============================================================
取所有未生成过的，不只是最新的版本
========================================================

----------------------------

select t1.page_issue_id,t1.snap_time,Max(t2.snap_time) as top2 from page_snapshot t1 left join page_snapshot t2
on t1.page_issue_id = t2.page_issue_id and t2.snap_time < t1.snap_time
group by t1.page_issue_id,t1.snap_time

p1   top1  top2  
p1   top2  top3  
p2   top1  top2

!!! lefe join 改 inner join  不然 top2会空

------------------------

------------------------------

select ra1.page_issue_id,ra1.top1,ra1.top2,ra2.snap_file as newfile ,ra2.make_step,ra2.make_file,ra2.make_message,ra3.snap_file as oldfile from
(
select t1.page_issue_id,t1.snap_time as top1,Max(t2.snap_time) as top2 from page_snapshot t1 left join page_snapshot t2
on t1.page_issue_id = t2.page_issue_id and t2.snap_time < t1.snap_time
group by t1.page_issue_id,t1.snap_time
)ra1
left join
(
select * from page_snapshot t1
)ra2
on ra1.page_issue_id = ra2.page_issue_id
and ra1.top1 = ra2.snap_time
left join
(
select t1.page_issue_id,t1.snap_time,t1.snap_file  from page_snapshot t1
)ra3
on ra1.page_issue_id = ra3.page_issue_id
and ra1.top2 = ra3.snap_time
where ra2.make_step=0 and ra1.top1 > to_date('2010-11-09','yyyy-mm-dd')


------------------------

select ra1.page_issue_id,ra1.top1,ra1.top2,ra2.snap_file as newfile, ra2.make_step,ra2.make_file,ra2.make_message,ra3.snap_file as oldfile from
(
select t1.page_issue_id,t1.snap_time as top1,Max(t2.snap_time) as top2
from page_snapshot t1
left join page_snapshot t2
on t1.page_issue_id = t2.page_issue_id and t2.snap_time < t1.snap_time
group by t1.page_issue_id,t1.snap_time
)ra1
left join
(select * from page_snapshot t1 )ra2
on ra1.page_issue_id = ra2.page_issue_id and ra1.top1 = ra2.snap_time
left join
(select t1.page_issue_id,t1.snap_time,t1.snap_file  from page_snapshot t1 )ra3
on ra1.page_issue_id = ra3.page_issue_id 
and ra1.top2 = ra3.snap_time
where ra2.make_step=0
and ra1.top1 > to_date('2010-11-08 12:57:30','yyyy-MM-dd HH24:mi:ss')