Oracle SQLʵÀý

1¡£select * from a where a.rowid=(select min(b.rowid) from b where a.id=b.id);
create test1(
nflowid number primary key, 
ndocid number,
drecvdate date);
insert into test1 values (1, 12301, sysdate) ;
insert into test1 values (2, 12301, sysdate);
select * from test1 order by drecvdate:
result:
nflowid ndocid drecvdate
1 12301 2010-2-1
2    12301 2010-2-2
ÒªÇó: ¸ù¾ÝNDOCIDΪ²éѯÌõ¼þ, DRECVDATEÅÅÐò,ÏÔʾÖظ´Êý¾ÝÖÐ×îСµÄÄÇÌõ¼Ç¼
String sql1 = "select * from test1 where ndocid=12301 order by decvdate";
String sql2 = "select * from (" + sql1 + ") a where a.rowid=" +
     "(select min(b.rowid) from (" + sql1 + ") b " +
     "where a.ndocid=b.ndocid)";
Òªµã: 1. nflowid ÊÇPK, Èç¹û²»ÊÇPK, ÔòÓпÉÄܻᱨ¸æORA-1445 ´íÎó
         2. ´ËÓï¾ä,²»¹ÜNFLOWID=1µÄDRECVDAGTE±ÈNFLOWID=2µÄDRECVDATEÔ绹ÊÇÍí,¶¼·µ»Ø:
             1, 12301 2010-2-1. Ô­ÒòÎÒ¾õµÃ:Õâ¸öROWIDòËƾÍÊÇNFLOWIDµÄÖµ
String agentSql = "select u.*, e.ndeputyentityid, e.nentityid " +
"from tbuser u, tbuser_role ur, doc_dept_deputy e " +
"where u.userid = ur.userid " +
"and u.currententityid = e.ndeputyentityid " +
"and ur.roleid=" + roleId;

String aSql = "select * from(" + agentSql + ") a where " +
"a.userpriority=(select min(b.userpriority) from(" + agentSql + ") b " +
"where a.ndeputyentityid = b.ndeputyentityid) " +
"and a.userid=(select min(b.userid) from(" + agentSql + ") b " +
"where a.ndeputyentityid = b.ndeputyentityid and a.userpriority = b.userpriority)";
Òªµã£º
1£© Èç¹ûuserpriorityûÓÐÖظ´Öµ£¬Ôò½á¹û·µ»Øuserpriority×îСֵµÄ¼Ç¼
2£© Èç¹ûuserpriorityÓÐÖظ´Öµ£¬Ôò½á¹û·µ»Øuserid×îСֵµÄ¼Ç¼