请教一个语句

[阳新互联]

我在给自己单位做个办公系统玩玩

其中包括IP，以及MAC地址的信息 楼下有80多台机器

记录IP的IpAddr字段是字符串型 192.168.80.15，192.168.80.11，192.168.80.114 ...
如果我使用

select * from IpInfo order by IpAddr asc

那么排序就是
192.168.80.11
192.168.80.114
192.168.80.15

如何才能正确的排序呢

请各位指教

[水族杰纶]

--這樣?
192.168.80.11
192.168.80.15
192.168.80.114

[CathySun118]

给你一个函数（将ip格式化为010.000.000.001的格式）：
CREATE FUNCTION dbo.f_1(@str varchar(8000),@c varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @s varchar(8000),@ret varchar(8000)
SET @s=LTRIM(RTRIM(@str))
SET @ret=''
WHILE CHARINDEX(@c,@s)>0
BEGIN
SET @ret=@ret+RIGHT('000'+LEFT(@s,CHARINDEX(@c,@s)-1),3)
SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(@c,@s))
END
SET @ret=@ret+RIGHT('000'+@s,3)
RETURN(@ret)
END
GO

--调用：
select * from ipinfo order by dbo.f_1(ipaddr,'.')

[百年树人]

order by cast(right(ipaddr,charindex('.',reverse(ipaddr))-1) as int)

[jiangshun]

select * from IpInfo order by replace(@s,'192.168.80.','') asc

[ai_li7758521]

ORDER BY right(IpAddr,len(IpAddr)-11) --假定前面都一样192.168.80.

[水族杰纶]

DECLARE @T TABLE(IP VARCHAR(50))
INSERT @T SELECT '192.168.80.11' 
INSERT @T SELECT '192.168.80.114' 
INSERT @T SELECT '192.168.80.15'
SELECT * FROM @T ORDER BY CAST(REVERSE(LEFT(REVERSE(IP),CHARINDEX('.',REVERSE(IP))-1))AS INT) 
/*IP                                                 
-------------------------------------------------- 
192.168.80.11
192.168.80.15
192.168.80.114
*/

[lg3605119]

select *from
(
select '192.168.80.11' AS IpAddr union all 
select '192.168.80.114' union all 
select '192.168.80.15' 
)T
order by cast(parsename(IpAddr,4) as int),cast(parsename(IpAddr,3) as int),cast(parsename(IpAddr,2) as int),cast(parsename(IpAddr,1) as int)

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ipaddr] varchar(14))
insert [tb]
select '192.168.80.11' union all
select '192.168.80.114' union all
select '192.168.80.15'
 
---查询---
select 
  * 
from 
  [tb] 
order by 
  cast(right(ipaddr,charindex('.',reverse(ipaddr))-1) as int)

---结果---
ipaddr         
-------------- 
192.168.80.11
192.168.80.15
192.168.80.114

（所影响的行数为 3 行）

[ai_li7758521]

修正：

ORDER BY CAST(right(IpAddr,len(IpAddr)-11) AS int) --假定前面都一样192.168.80.

[liangCK]

[Quote=引用 2 楼 CathySun118 的回复:]
给你一个函数（将ip格式化为010.000.000.001的格式）：
CREATE  FUNCTION  dbo.f_1(@str  varchar(8000),@c  varchar(10))
RETURNS  varchar(8000)
AS
BEGIN
DECLARE  @s  varchar(8000),@ret  varchar(8000)
SET  @s=LTRIM(RTRIM(@str))
SET  @ret=''
WHILE  CHARINDEX(@c,@s)>0
BEGIN
SET  @ret=@ret+RIGHT('000'+LEFT(@s,CHARINDEX(@c,@s)-1),3)
SET  @s=RIGHT(@s,LEN(@s)-CHARINDEX(@c,@s))
END
S…
[/Quote]

支持这个.

[ai_li7758521]

if object_id('[tb]') is not null drop table [tb]
go
CREATE TABLE [tb]([ipaddr] VARCHAR(14))
INSERT [TB]
SELECT '192.168.80.11' UNION ALL
SELECT '192.168.80.114' UNION ALL
SELECT '192.168.80.15'
 
SELECT * FROM [TB] 
 ORDER BY CAST(right(IpAddr,len(IpAddr)-11) AS int)

ipaddr
--------------
192.168.80.11
192.168.80.15
192.168.80.114

(3 行受影响)

[nalnait]

declare @IpInfo table(ip varchar(20))
insert into @IpInfo
select '192.168.80.11' union all
select '192.168.80.114' union all
select '192.168.80.15'

select * from @IpInfo order by cast(replace(ip,'.','')as bigint) 
/*
ip
--------------------
192.168.80.11
192.168.80.15
192.168.80.114

(3 行受影响)
*/

[zhaoyh0530]

[Quote=引用 10 楼 liangCK 的回复:]
引用 2 楼 CathySun118 的回复:
给你一个函数（将ip格式化为010.000.000.001的格式）：
CREATE FUNCTION dbo.f_1(@str varchar(8000),@c varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @s varchar(8000),@ret varchar(8000)
SET @s=LTRIM(RTRIM(@str))
SET @ret=''
WHILE CHARINDEX(@c,@s)>0
BEGIN
SET @ret=@ret+RIGHT('000'+LEFT(@s,CHARINDEX(@c,@s)-1),3)
SET @s=…
[/Quote]谢谢有学习啦