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最小值的记录