日期:2014-05-18 浏览次数:20633 次
--try select substring(通讯地址,1,13),min(substring(通讯地址,15,3)) from A group by substring(通讯地址,1,13)
------解决方案--------------------
declare @t table([通讯地址] varchar(17)) insert @t select '192.168.1.111,800' union all select '192.168.1.111,801' union all select '192.168.1.250,801' union all select '192.168.1.250,802' union all select '192.168.1.250,803' union all select '192.168.1.251,805' select * from @t t where right([通讯地址],3)= (select min(right([通讯地址],3)) from @t where left([通讯地址],13)=left(t.[通讯地址],13)) /* 通讯地址 ----------------- 192.168.1.111,800 192.168.1.250,801 192.168.1.251,805 */ --也可以用逗号做标记分割
------解决方案--------------------
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba') BEGIN DROP TABLE tba END GO CREATE TABLE tba ( ID INT IDENTITY(1,1), IP VARCHAR(100) ) GO INSERT INTO tba SELECT '192.168.1.111,800' UNION SELECT '192.168.1.111,801'UNION SELECT '192.168.1.250,801'UNION SELECT '192.168.1.250,802'UNION SELECT '192.168.1.250,803'UNION SELECT '192.168.1.251,805' GO SELECT * FROM tba AS A WHERE IP = (SELECT TOP 1 IP FROM tba WHERE LEFT(IP,CHARINDEX(',',IP)) = LEFT(A.IP,CHARINDEX(',',A.IP))) ID IP 1 192.168.1.111,800 3 192.168.1.250,801 6 192.168.1.251,805