Âé·³¸ø¿´¿´Õâ¸öoracleÓï¾ä¸ÃÔõÑùд

±íÃû£ºgk_record_new£¨ÕâÊÇÒ»¸ö¿¼ÇÚ±í£¬ËùÓÐÔ±¹¤µÄ¿¼ÇÚÊý¾Ý¶¼´æ·ÅÔÚÕâ¸ö±íÀÖ÷¼üΪ¿¨ºÅºÍʱ¼ä£©

±í½á¹¹£º
¿¨ºÅ£ºNUMBER(10)
»ùÕ¾£ºVARCHAR2(20)
ʱ¼ä£ºDATE
³öÈë±êÖ¾£ºVARCHAR2(1)

±íÊý¾Ý

¿¨ºÅ »ùÕ¾ ʱ¼ä ³öÈë±êÖ¾
123 1# ... I
456 2# ... I
123 1# ... O


´ÓÕâ¸ö±íÀï»ñÈ¡µ±Ç°ÔÚ°àÉϵÄÈË

ÎÒдµÄÓï¾äÊÇ£º
select * from (select * from gk_record_new where ³öÈë±êÖ¾='I' order by ʱ¼ä desc) where rownum<=4 order by ʱ¼ä desc

ÆäÖС°4¡±ÊÇËùÓнøÈëµÄÈËÔ±¼õÈ¥³öÈ¥µÄ²îÖµ£¬²»¹ýÕâÑùдºÃÏñ²»¶Ô

Çë¸ßÊÖ°ï°ïæ ³îËÀÁË....

---

select * from
(select distinct id,
  (select max(date) from t where t.id = id and type = 'I') din,
  (select max(date) from t where t.id = id and type = 'O') dout
from t)
where din > dout and din < sysdate;

---

select * from (
  select cardno,mod(count(cardno),2) num from GK_RECORD_NEW group by cardno
) t, GK_RECORD_NEW g where t.num = 1 and g.io='I' and t.cardno = g.cardno

---

select *
  from (
select ¿¨ºÅ,»ùÕ¾,ʱ¼ä,³öÈë±êÖ¾,row_number()over(partition by ¿¨ºÅ order by ʱ¼ä desc) rn from gk_record_new )
where rn=1 and ³öÈë±êÖ¾='I'
;

---

Â¥Ö÷µÄ
select * from (select * from gk_record_new where ³öÈë±êÖ¾='I' order by ʱ¼ä desc) where rownum <=4 order by ʱ¼ä desc 
¸Ð¾õÊDz»¶Ô Ê×ÏȲé³ö