在日常开发需求中，我们有时会遇到获取表中前n条记录的情况.
例如登录日志表 login_log_info 中有id, user_name, client_ip, login_in, create_time, brow 这几个字段。
具体数据有：
ID | USER_NAME | client_ip | LOGIN_IN | CREATE_TIME | browser_name
------------------+-------------+-----------------+----------+---------------------+-------------
067sigy4b1jrg9mm | root | 0:0:0:0:0:0:0:1 | 1 | 2018-02-08 17:05:05 | Chrome
0juhg2nyg2pivbku | root | 127.0.0.1 | 1 | 2018-02-07 07:49:02 | Chrome
1fz8h6f6al2zezbx | root | 127.0.0.1 | 1 | 2018-02-06 17:08:19 | Chrome
1lxx8ipeca0zu6un | root | 127.0.0.1 | 1 | 2018-02-06 17:22:59 | Chrome
1s5jf7ye4o5xwg3w | root | 0:0:0:0:0:0:0:1 | 1 | 2018-02-08 17:05:14 | Chrome
2v4f2dz0v975541e | root | 127.0.0.1 | 1 | 2018-03-06 14:17:40 | Chrome
3qvkrl67eze3j9nn | root | 127.0.0.1 | 1 | 2018-02-07 07:50:11 | Chrome
6iapi5cvfzdu09hc | root | 127.0.0.1 | 0 | 2018-02-06 17:34:42 | Chrome
7ef3lrz92u7wxzhw | root | 127.0.0.1 | 0 | 2018-02-06 17:07:57 | Chrome
84mw0b8ubx7f7rzq | root | 0:0:0:0:0:0:0:1 | 1 | 2018-03-06 14:16:32 | Chrome
87hlq2blcod903za | zhanghui | 127.0.0.1 | 0 | 2018-02-06 18:33:04 | Chrome
8rji1f18vsrmhu88 | root | 172.16.10.215 | 1 | 2018-02-27 15:55:39 | Chrome
awzp6qujj0mobz3j | root | 0:0:0:0:0:0:0:1 | 1 | 2018-03-19 09:09:44 | Chrome
d17k3g6uidf0rm8a | root | 0:0:0:0:0:0:0:1 | 1 | 2018-03-06 15:27:00 | Chrome
fgqhrg2eml4edv1s | root | 127.0.0.1 | 1 | 2018-02-06 18:27:55 | Chrome
fx1yy48w8dijbmje | root | 0:0:0:0:0:0:0:1 | 1 | 2018-02-08 17:17:28 | Chrome
gkyoop78qerdejh1 | root | 127.0.0.1 | 1 | 2018-02-06 18:33:09 | Chrome
ir6ziil3gg3kphvx | root | 127.0.0.1 | 1 | 2018-02-06 17:34:51 | Chrome
kecx6npljo1ini0v | root | 127.0.0.1 | 1 | 2018-02-07 11:04:09 | Chrome
mgwpytl326z5hdv2 | root | 172.16.10.215 | 1 | 2018-02-27 15:55:39 | Chrome
n1r5lsqlrdtv0r7d | root | 127.0.0.1 | 1 | 2018-02-06 17:14:35 | Chrome
pn9nweq6j4nd4jff | root | 127.0.0.1 | 0 | 2018-02-06 17:03:49 | Chrome
ruz6vfj9y8muxw97 | root | 0:0:0:0:0:0:0:1 | 1 | 2018-02-08 17:25:22 | Chrome
tqpzdirobiibi631 | root | 127.0.0.1 | 0 | 2018-02-06 18:32:56 | Chrome
txs0c1wf1hshjk95 | root | 127.0.0.1 | 1 | 2018-02-06 17:54:29 | Chrome
wy12gvdb0v6gi6fi | root | 127.0.0.1 | 1 | 2018-02-06 18:32:45 | Chrome
yt9lk2qbrkxuooyy | root | 127.0.0.1 | 0 | 2018-02-06 17:13:55 | Chrome
zfwjjtmmvi70qlmz | root | 127.0.0.1 | 0 | 2018-02-06 17:13:14 | Chrome
求出 前2位最多的client_ip。
一、mysql实现方式
select client_ip, count(1) num
from login_log_info l
group by l.client_ip
order by num desc
limit 0,2;
结果为：
client_ip | num
-----------------+-----
127.0.0.1 | 19
0:0:0:0:0:0:0:1 | 7
二、oracle 实现方式
select * form (
select client_ip, count(1) num
from login_log_info l
group by l.client_ip
order by num desc
)
where ROWNUM < = 2
三、总结
mysql 是通过 limit startIndex,number 其中 startIndex是起始位置，number是要查询的数量 来实现的
oracle 是通过 ROWNUM 来实现的
转载自原文链接, 如需删除请联系管理员。
原文链接：TOP n 在mysql 与 oracle 中的区别，转载请注明来源！