Âé·³¸ø¿´¿´Õâ¸ö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»¶Ô Ê×ÏȲé³ö
Ïà¹ØÎÊ´ð£º
»·¾³£º1.win2003server+oracle9i
2.oracle9i×Ö·û¼¯ÎªAMERICAN_AMERICA.WE8ISO8859P1
3.oracle sql developer°æ±¾ 1.5.5
ÏÖÏóÃèÊö: 1.ÔÚsql developer ÖвéѯoracleÖеÄij¸ö±í£¬ÖÐÎÄÈ«²¿ÏÔʾΪÂÒÂë¡£
......
°²×°11gµÄ06,´ò²¹¶¡Éýµ½07£¬´ÓODBCÀïÃæ¿´£¬ÓÐ
Oracle in OraOdac11g_home1 11.01.00.07
´Ó×¢²á±í¿´HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ÏÂÃæµÄDriverºÍSetup¶¼ÊÇÎҵݲװ·¾¶
"D:\\app\\Ad ......
ÒÔÇ°³ÌÐòºÃºÃµÄ,ÄܽøÐÐÈκβÙ×÷
µ«´Ó×òÌ쿪ʼͻȻÁ¬½ÓÊý¾Ý¿âʧ°Ü,´úÂëÈçÏÂ:
try
{
::CoInitialize(NULL);
hr=m_pConnection.Creat ......
ÏÖÔÚÓÐÒ»ORACLEÖеÄSQLÓï¾ä£¬ÐèÒªÒÆÖ²µ½DB2ÖУ¬ÇëÎʸÃSQL¸ÄÈçºÎд
ORACLEÖУº
select floor(months_between(date1,date2)) from A
date1,date2·Ö±ðΪ±íÖеÄÁ½¸ö×ֶΠ£¬¶¼ÎªÈÕÆÚÐÍ
DB2ÖÐÈçºÎʹÓÃÐ ......
ÏÖÓÐÒÔÏÂÊý¾Ý£º
ID Name
1 Jack,Tom,Ben
2 Mary,Simth,Tony,Jay
ת»»Îª£º
ID Name
1 Jack
1 Tom
1 Ben
2 Mary
2 Simth
2 Tony
2 Jay
ÒªÇóʹÓÃSQL²éѯÍê³É£¬ÓÉÓÚÌõ¼þÏÞÖÆ£¬²»ÄÜʹÓà ......