DELPHI盒子
!实时搜索: 盒子论坛 | 注册用户 | 修改信息 | 退出
检举帖 | 全文检索 | 关闭广告 | 捐赠
技术论坛
 用户名
 密  码
自动登陆(30天有效)
忘了密码
≡技术区≡
DELPHI技术
移动应用开发
Web应用开发
数据库专区
报表专区
网络通讯
开源项目
论坛精华贴
≡发布区≡
发布代码
发布控件
文档资料
经典工具
≡事务区≡
网站意见
盒子之家
招聘应聘
信息交换
论坛信息
最新加入: cetacean66
今日帖子: 16
在线用户: 13
导航: 论坛 -> 数据库专区 斑竹:liumazi,waterstone  
作者:
男 dalas (dalas) ★☆☆☆☆ -
普通会员
2021/7/16 1:28:02
标题:
请教个 SQLite 对 IP 按数字排序的问题 浏览:539
加入我的收藏
楼主: 对IP字段,按数字排序,在 SQL Server 里如下写法:
select * 
from IPList
order by cast(parsename(ip,4) as int),cast(parsename(ip,3) as int),cast(parsename(ip,2) as int),cast(parsename(ip,1) as int)

请帮忙改成 SQLite 的,谢谢!!
----------------------------------------------
-
作者:
男 emailx45 (emailx45) ▲▲▲△△ -
注册会员
2021/7/16 5:03:08
1楼: I dont understand very well, but...

https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-ver15

"Arguments
'object_name' Is the parameter that holds the name of the object for which to retrieve the specified object part. This parameter is an optionally-qualified object name. If all parts of the object name are qualified, this name can have four parts: the server name, the database name, the schema name, and the object name. Each part of the 'object_name' string is type sysname which is equivalent to nvarchar(128) or 256 bytes. If any part of the string exceeds 256 bytes, PARSENAME will return NULL for that part as it is not a valid sysname.

object_piece
Is the object part to return. object_piece is of type int, and can have these values:
1 = Object name
2 = Schema name
3 = Database name
4 = Server name
"

SQL

Copy
-- Uses AdventureWorks  
  
SELECT PARSENAME('AdventureWorksPDW2012.dbo.DimCustomer', 1) AS 'Object Name';  
SELECT PARSENAME('AdventureWorksPDW2012.dbo.DimCustomer', 2) AS 'Schema Name';  
SELECT PARSENAME('AdventureWorksPDW2012.dbo.DimCustomer', 3) AS 'Database Name';  
SELECT PARSENAME('AdventureWorksPDW2012.dbo.DimCustomer', 4) AS 'Server Name';  
GO  

Here is the result set.


Copy
Object Name
----------
DimCustomer

(1 row(s) affected)

Schema Name
----------
dbo

(1 row(s) affected)

Database Name
----------
AdventureWorksPDW2012

(1 row(s) affected)

Server Name
----------
(null)

(1 row(s) affected)

---------- my note ----------
in your Database you would can use 4 fields (Byte type) to store each part of IP4, or 1 Field (String type) with the IP whole. Or, a field (String type) with Hexa value for each IP4. 

For IP6, the same idea with size necessary for each part, of course!
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!
作者:
男 emailx45 (emailx45) ▲▲▲△△ -
注册会员
2021/7/16 5:07:42
2楼: does some sample stored in your MS SQL Database for analize?

table: IPList:
----------
IP field
xxxxx   <---- What is store in each row of the table? what is the info?
xxxxx
xxxxx
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!
作者:
男 emailx45 (emailx45) ▲▲▲△△ -
注册会员
2021/7/16 5:14:09
3楼: maybe help using INET_ATON() SQLite function?

https://stackoverflow.com/questions/1270855/what-is-the-sql-server-equivalent-of-inet-aton

see the "Stored Procedure" above:

CREATE FUNCTION dbo.ipStringToInt 

    @ip CHAR(15) 

RETURNS INT 
AS 
BEGIN 
    DECLARE @rv INT, 
        @o1 INT, 
        @o2 INT, 
        @o3 INT, 
        @o4 INT, 
        @base INT 

    SELECT 
        @o1 = CONVERT(INT, PARSENAME(@ip, 4)), 
        @o2 = CONVERT(INT, PARSENAME(@ip, 3)), 
        @o3 = CONVERT(INT, PARSENAME(@ip, 2)), 
        @o4 = CONVERT(INT, PARSENAME(@ip, 1)) 

    IF (@o1 BETWEEN 0 AND 255) 
        AND (@o2 BETWEEN 0 AND 255) 
        AND (@o3 BETWEEN 0 AND 255) 
        AND (@o4 BETWEEN 0 AND 255) 
    BEGIN      
        SELECT @base = CASE 
          WHEN @o1 < 128 THEN 
          (@o1 * 16777216) 
          ELSE 
          -(256 - @o1) * 16777216 
          END 

        SET @rv = @base +  
          (@o2 * 65536) +  
          (@o3 * 256) + 
          (@o4) 
    END 
    ELSE 
        SET @rv = -1 
    RETURN @rv 
END

-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

INSERT mytable VALUES(dbo.ipStringToInt('1.2.3.4'))

-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

CREATE FUNCTION dbo.ipIntToString 

    @ip bigINT 

RETURNS CHAR(15) 
AS 
BEGIN 
    DECLARE @o1 bigINT, 
        @o2 bigINT, 
        @o3 bigINT, 
        @o4 bigINT 

    IF ABS(@ip) > 4294967295 
        RETURN '255.255.255.255' 

    SET @o1 = @ip / 16777216 

    IF @o1 = 0 
        SELECT @o1 = 255, @ip = @ip + 16777216 

    ELSE IF @o1 < 0 
    BEGIN 
        IF @ip % 16777216 = 0 
          SET @o1 = @o1 + 256 
        ELSE 
        BEGIN 
          SET @o1 = @o1 + 255 
          IF @o1 = 128 
          SET @ip = @ip + 2147483648 
          ELSE 
          SET @ip = @ip + (16777216 * (256 - @o1)) 
        END 
    END 
    ELSE 
    BEGIN 
        SET @ip = @ip - (16777216 * @o1) 
    END 

    SET @ip = @ip % 16777216 
    SET @o2 = @ip / 65536 
    SET @ip = @ip % 65536 
    SET @o3 = @ip / 256 
    SET @ip = @ip % 256 
    SET @o4 = @ip 

    RETURN 
        CONVERT(VARCHAR(4), @o1) + '.' + 
        CONVERT(VARCHAR(4), @o2) + '.' + 
        CONVERT(VARCHAR(4), @o3) + '.' + 
        CONVERT(VARCHAR(4), @o4) 
END
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!
作者:
男 dalas (dalas) ★☆☆☆☆ -
普通会员
2021/7/16 23:55:23
4楼: 搞定了:
      SQL.Add('select IP, Name, Typ, Status');
      SQL.Add(Format(',to_number(substr(ip,1,instr(ip,%0:s%1:s%0:s,1,1)-1)) as ip1',['''','.']));
      SQL.Add(Format(',to_number(substr(ip,instr(ip,%0:s%1:s%0:s,1,1)+1,instr(ip,%0:s%1:s%0:s,1,2)-instr(ip,%0:s%1:s%0:s,1,1)-1)) as ip2',['''','.']));
      SQL.Add(Format(',to_number(substr(ip,instr(ip,%0:s%1:s%0:s,1,2)+1,instr(ip,%0:s%1:s%0:s,1,3)-instr(ip,%0:s%1:s%0:s,1,2)-1)) as ip3',['''','.']));
      SQL.Add(Format(',to_number(substr(ip,instr(ip,%0:s%1:s%0:s,1,3)+1,length(ip)+1-instr(ip,%0:s%1:s%0:s,1,3))) as ip4',['''','.']));
      SQL.Add('from   IPList');
      SQL.Add('order by ip1, ip2, ip3, ip4'); //SQLite/Oracle 对 IP 按数字排序
----------------------------------------------
-
作者:
男 emailx45 (emailx45) ▲▲▲△△ -
注册会员
2021/7/17 3:33:27
5楼: 我并没有真正理解你上面的“SQL”,因为我不是每天都使用“MSSQL/MySQL”...

然而,为了在“SQL”语言中更有效地使用“ORDER BY/INDEX”,我更喜欢只有一个字段。 也就是说,例如,由于您可以使用唯一的数值(整数或 BigInt / Int64)获得“IP”,因此您不需要生成复合索引( ORDER BY ip1, ip2, ip3, ip4 ),但只是一个“ORDER BY MyIPnumericValue”,它将是 IP 地址的值,但是,采用整数格式(Integer / BigInt 或 Int64 甚至 HEXA)。 - 由于数值代表IP字符串值,其中ip-integer-greater = ip-string-greater

1.1.1.1      = 16843009
255.255.255.255 = 4294967295

所以我认为你的“INDEX”“SQL”会更有效地使用。

这个想法怎么样?
----------
I didn't really understand your "SQL" above, because I don't use "MSSQL / MySQL" on a daily basis...

However, for a more performative use of "ORDER BY/INDEX" in the "SQL" language, I prefer to have only one field. That is, since you can have your "IP" with a unique numeric value (an Integer or BigInt / Int64), for example, then you would not need to produce a composite index ( ORDER BY ip1, ip2, ip3, ip4 ), but simply an "ORDER BY MyIPnumericValue", where it would be the value of the IP address, however, in integer format (Integer / BigInt or Int64 or even in HEXA). - Since the numeric value represents the IP string value, where ip-integer-greater = ip-string-greater

1.1.1.1      = 16843009
255.255.255.255 = 4294967295

So I think your "INDEX" "SQL" would be more efficient to use.

How about the idea? Or, some like this...

SELECT 
 ipAddress {xxx.xxx.xxx.xxx}.
 Name,
 Typ,
 Status,
 MyFuncReturnIPinFormatNumericOrString as IPtoOrder 
FROM
 IpList
ORDER BY IPtoOrder
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!
作者:
男 dalas (dalas) ★☆☆☆☆ -
普通会员
2021/7/17 8:19:12
6楼: 我上面的代码是放到 TQuery 里执行的 Delphi 代码,实际执行的 SQL 脚本如下:
select IP, Name, Typ, Status
,to_number(substr(ip,1,instr(ip,'.',1,1)-1)) as ip1
,to_number(substr(ip,instr(ip,'.',1,1)+1,instr(ip,'.',1,2)-instr(ip,'.',1,1)-1)) as ip2
,to_number(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3)-instr(ip,'.',1,2)-1)) as ip3
,to_number(substr(ip,instr(ip,'.',1,3)+1,length(ip)+1-instr(ip,'.',1,3))) as ip4
from   IPList
order by ip1, ip2, ip3, ip4
;

原理其实就是把 IP 拆分成4位数字,再按数字排序。
你上面的代码,也是一个不错的办法。
----------------------------------------------
-
作者:
男 emailx45 (emailx45) ▲▲▲△△ -
注册会员
2021/7/18 5:13:59
7楼: try my code
http://bbs.2ccc.com/topic.asp?topicid=615219
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!
信息
登陆以后才能回复
Copyright © 2CCC.Com 盒子论坛 v2.1 版权所有 页面执行15.625毫秒 RSS