select distinct t.GDB_NBR,t.AUDIT_TIME,t.CARD_ID from( SELECT distinct A.GDB_NBR,A.AUDIT_TIME,A.CARD_ID, row_number() over (partition by tab.OPERID order by A.AUDIT_TIME desc) rnum FROM GDB_BASE_APPROVE A
INNER JOIN GDB_BASE_BANK B ON A.GDB_NBR=B.GDB_NBR INNER JOIN GDB_BASE_INFO I ON B.GDB_NBR=I.GDB_NBR AND Date(A.AUDIT_TIME) between '2011-01-13' and '2011-01-17'
INNER JOIN GDB_TELVERIFY tab ON B.GDB_NBR=tab.GDB_NBR ) t where t.rnum =1 ORDER BY t.AUDIT_TIME DESC fetch first 10 rows only with ur
INNER JOIN GDB_BASE_BANK B ON A.GDB_NBR=B.GDB_NBR INNER JOIN GDB_BASE_INFO I ON B.GDB_NBR=I.GDB_NBR AND Date(A.AUDIT_TIME) between '2011-01-13' and '2011-01-17'
INNER JOIN GDB_TELVERIFY tab ON B.GDB_NBR=tab.GDB_NBR ) t where t.rnum =1 ORDER BY t.AUDIT_TIME DESC fetch first 10 rows only with ur
前面就是
select distinctinner join
from(
SELECT distinct A.GDB_NBR,A.AUDIT_TIME,A.CARD_ID, row_number() over (partition by tab.OPERID order by A.AUDIT_TIME desc) rnum
FROM GDB_BASE_APPROVE A
INNER JOIN GDB_BASE_BANK B ON A.GDB_NBR=B.GDB_NBR
INNER JOIN GDB_BASE_INFO I ON B.GDB_NBR=I.GDB_NBR AND Date(A.AUDIT_TIME) between '2011-01-13' and '2011-01-17'
INNER JOIN GDB_TELVERIFY tab ON B.GDB_NBR=tab.GDB_NBR ) t
where t.rnum =1
ORDER BY t.AUDIT_TIME DESC
fetch first 10 rows only with ur
从三个基表中获取满足条件的数据,经排序后输出.
这里的fetch不清楚是啥意思.
子句的作用是指只取前 n 行,类似于SQL 里的top n
t 则是 ( SELECT distinct A.GDB_NBR,A.AUDIT_TIME,A.CARD_ID, row_number() over (partition by tab.OPERID order by A.AUDIT_TIME desc) rnum FROM GDB_BASE_APPROVE A
INNER JOIN GDB_BASE_BANK B ON A.GDB_NBR=B.GDB_NBR INNER JOIN GDB_BASE_INFO I ON B.GDB_NBR=I.GDB_NBR AND Date(A.AUDIT_TIME) between '2011-01-13' and '2011-01-17'
INNER JOIN GDB_TELVERIFY tab ON B.GDB_NBR=tab.GDB_NBR )