Oracle中,如果非得要用SQL来对IP地址进行排序的话:
select ip,
to_number(substr(ip, 1, instr(ip, '.', 1, 1)-1)) newip1,
to_number(substr(ip, instr(ip,'.',1,1)+1, instr(ip, '.', 1, 2)-instr(ip,'.',1,1)-1)) newip2,
to_number(substr(ip, instr(ip,'.',1,2)+1, instr(ip, '.', 1, 3)-instr(ip,'.',1,2)-1)) newip3,
to_number(substr(ip, instr(ip,'.',1,3)+1, length(ip)+1-instr(ip,'.',1,3))) newip4
from net_gameip
order by newip1, newip2, newip3, newip4
结果:
IP NEWIP1 NEWIP2 NEWIP3 NEWIP4
--------------- ---------- ---------- ---------- ----------
1.2.3.8 1 2 3 8
1.2.3.9 1 2 3 9
1.2.3.10 1 2 3 10
1.2.3.12 1 2 3 12
1.2.3.13 1 2 3 13
1.2.3.14 1 2 3 14
1.2.3.15 1 2 3 15
1.2.3.16 1 2 3 16
1.2.3.17 1 2 3 17
1.2.3.19 1 2 3 19
1.2.3.20 1 2 3 20
1.2.3.21 1 2 3 21
1.2.3.22 1 2 3 22
1.2.3.23 1 2 3 23
2.1.1.1 2 1 1 1
2.1.2.1 2 1 2 1
2.2.2.2 2 2 2 2
10.1.1.1 10 1 1 1
10.1.1.1 10 1 1 1
10.1.1.2 10 1 1 2
10.1.1.2 10 1 1 2