如何写这样的SQL？

Question

假设表名：ipList，ip字段为ip_long用户输入的ip段（数字ip）0.0.0.0到255.255.255.255，权限要求他只能查看2046820352 and 2097151999,3523215361 and 3556769791,385875969 and 855638015
这些ip段之间的记录，ip段转换前字符串形式如下122.0.0.0

124.255.255.255

210.0.0.1

211.255.255.255

23.0.0.1

50.255.255.255假设表名：ipList，ip字段：ip_long小弟是做程序的，请问这样的sql语句应该怎么写？谢谢

Answer 1

-- TRY IT ..
SQL> SELECT * FROM IP_LIST;IP_LONG
---------------
255.255.255.255
0.0.0.100
100.0.1.1
100.0.0.1
10.23.34.277
0.0.0.16 rows selectedSQL> SELECT * FROM USER_IPS;    USERID MIN_IP  MAX_IP
---------- ------- ---------
         1 0.0.0.0 100.0.0.0SQL> SELECT DISTINCT IP_LONG
  2    FROM (SELECT RN1,
  3                 TO_NUMBER(REPLACE(WMSYS.WM_CONCAT(NEW_IP_LONG)
  4                                   OVER(PARTITION BY RN1),
  5                                   ',',
  6                                   '')) NEW_IP_LONG,
  7                 IP_LONG
  8            FROM (SELECT RN1,
  9                         LPAD(SUBSTR('.' || IP_LONG || '.',
 10                                     INSTR('.' || IP_LONG || '.', '.', 1, RN2) + 1,
 11                                     INSTR('.' || IP_LONG || '.',
 12                                           '.',
 13                                           1,
 14                                           RN2 + 1) -
 15                                     INSTR('.' || IP_LONG || '.', '.', 1, RN2) - 1),
 16                              3,
 17                              '0') NEW_IP_LONG,
 18                         IP_LONG
 19                    FROM (SELECT ROWNUM RN1, TT.*
 20                            FROM IP_LIST TT) YY,
 21                         (SELECT ROWNUM RN2 FROM DUAL CONNECT BY ROWNUM <= 3) ZZ
 22                   WHERE INSTR('.' || IP_LONG || '.', '.', 1, RN2) > 0
 23                   ORDER BY RN1, RN2) SS) T1,
 24         (SELECT RN1,
 25                 TO_NUMBER(REPLACE(WMSYS.WM_CONCAT(MIN_IP), ',', '')) MIN_IP,
 26                 TO_NUMBER(REPLACE(WMSYS.WM_CONCAT(MAX_IP), ',', '')) MAX_IP
 27            FROM (SELECT USERID RN1,
 28                         LPAD(SUBSTR('.' || MIN_IP || '.',
 29                                     INSTR('.' || MIN_IP || '.', '.', 1, RN2) + 1,
 30                                     INSTR('.' || MIN_IP || '.', '.', 1, RN2 + 1) -
 31                                     INSTR('.' || MIN_IP || '.', '.', 1, RN2) - 1),
 32                              3,
 33                              '0') MIN_IP,
 34                         LPAD(SUBSTR('.' || MAX_IP || '.',
 35                                     INSTR('.' || MAX_IP || '.', '.', 1, RN2) + 1,
 36                                     INSTR('.' || MAX_IP || '.', '.', 1, RN2 + 1) -
 37                                     INSTR('.' || MAX_IP || '.', '.', 1, RN2) - 1),
 38                              3,
 39                              '0') MAX_IP
 40                    FROM USER_IPS UT,
 41                         (SELECT ROWNUM RN2 FROM DUAL CONNECT BY ROWNUM <= 3) ZZ
 42                   WHERE INSTR('.' || MIN_IP || '.', '.', 1, RN2) > 0
 43                   ORDER BY USERID, RN2) SS
 44           GROUP BY RN1) T2
 45   WHERE NEW_IP_LONG BETWEEN MIN_IP AND MAX_IP;IP_LONG
---------------
0.0.0.1
0.0.0.100
10.23.34.277

Answer 2

给你看两个我写的函数
第一是把像192.168.1.1转换成数字
CREATE OR REPLACE FUNCTION sf_ip2num (in_ip VARCHAR)
   RETURN NUMBER
IS
   tmpvar   NUMBER;
/******************************************************************************
   NAME:       SF_IP2NUM
   PURPOSE:   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2008/8/28   He Bo       1. Created this function.   NOTES:   Automatically available Auto Replace Keywords:
      Object Name:     SF_IP2NUM
      Sysdate:         2008/8/28
      Date and Time:   2008/8/28, 11:59:28, and 2008/8/28 11:59:28
      Author:        He Bo
      Table Name:       (set in the "New PL/SQL Object" dialog)******************************************************************************/
BEGIN
   tmpvar := 0;   SELECT   TO_NUMBER (  SUBSTR (in_ip, 1, INSTR (in_ip, '.', 1) - 1)
                       * 255
                       * 255
                       * 255
                      )
          +   TO_NUMBER (SUBSTR (in_ip,
                                 INSTR (in_ip, '.', 1) + 1,
                                   INSTR (in_ip, '.', 1, 2)
                                 - INSTR (in_ip, '.', 1)
                                 - 1
                                )
                        )
            * 255
            * 255
          +   TO_NUMBER (SUBSTR (in_ip,
                                 INSTR (in_ip, '.', 1, 2) + 1,
                                   INSTR (in_ip, '.', 1, 3)
                                 - INSTR (in_ip, '.', 1, 2)
                                )
                        )
            * 255
          + TO_NUMBER (SUBSTR (in_ip, INSTR (in_ip, '.', 1, 3) + 1))
     INTO tmpvar
     FROM DUAL;   RETURN tmpvar;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN 0;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RETURN 0;
END sf_ip2num;
/第二个是查找IP所属位置CREATE OR REPLACE FUNCTION sf_get_ip_loc (in_ip VARCHAR2)
   RETURN VARCHAR2
IS
   tmpvar   VARCHAR2(500);
/******************************************************************************
   NAME:       SF_GET_IP_LOC
   PURPOSE:   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2008/8/28   He Bo       1. Created this function.   NOTES:   Automatically available Auto Replace Keywords:
      Object Name:     SF_GET_IP_LOC
      Sysdate:         2008/8/28
      Date and Time:   2008/8/28, 12:57:40, and 2008/8/28 12:57:40
      Author:        He Bo
      Table Name:       (set in the "New PL/SQL Object" dialog)******************************************************************************/
BEGIN
   SELECT country || '-' ||local
     INTO tmpvar
     FROM tb_ipdata
    WHERE sf_ip2num (in_ip) BETWEEN start_ip_num AND end_ip_num AND ROWNUM = 1;   RETURN tmpvar;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN '未知地点';
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RETURN '查找IP地点异常错误';
END sf_get_ip_loc;
/

Answer 3

select * from iplist
where to_number(substr(ip_long,1,instr(ip_long,'.')-1)) between 1 and 99

Answer 4

CREATE OR REPLACE FUNCTION ConvertIP(p_IP VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
    RETURN regexp_replace('00' || REPLACE(p_IP, '.', '.00'), '\d+(\d{3})', '\1');
END;
/SELECT * FROM ipList WHERE ConvertIP(IP_Long) BETWEEN ConvertIP('0.0.0.0') AND ConvertIP('100.0.0.0');