日期:2014-05-16 浏览次数:20807 次
/*
CREATE TABLE CUSTOM(
CUSTOMID NUMBER(20),
NAME VARCHAR2(40)
)
;
CREATE TABLE LMSG(
CUSTOMID NUMBER(20),
CREDIT NUMBER(20),
BANKNAME VARCHAR2(40)
)
;
INSERT INTO CUSTOM VALUES(1,'小明');
INSERT INTO CUSTOM VALUES(2,'小芳');
INSERT INTO LMSG VALUES(1,100,'农行');
INSERT INTO LMSG VALUES(1,200,'中国银行');
INSERT INTO LMSG VALUES(1,200,'建设银行');
INSERT INTO LMSG VALUES(2,150,'建设银行');
INSERT INTO LMSG VALUES(2,200,'农行');
*/
SELECT CUSTOMID,NAME,CREDIT,BANKNAME FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY A.CUSTOMID ORDER BY B.CREDIT DESC, BANKNAME) RN,
A.CUSTOMID,A.NAME,B.CREDIT,B.BANKNAME
FROM CUSTOM A, LMSG B
WHERE A.CUSTOMID = B.CUSTOMID)
WHERE RN=1;