日期:2014-05-18  浏览次数:20633 次

急!!帮忙写SQL语句
我想取出分组后满足每条分组记录的第一条记录
如有1个字段

  通讯地址
192.168.1.111,800
192.168.1.111,801
192.168.1.250,801
192.168.1.250,802
192.168.1.250,803
192.168.1.251,805

我现在想按前半部分分组 然后取出每组分组后的第一条记录

既得到如下记录
通讯地址
192.168.1.111,800
192.168.1.250,801
192.168.1.251,805

有人能帮实现一下吗?写不出来呢 现在只能分组
select substring(通讯地址,1,13) from A group by substring(通讯地址,1,13)

------解决方案--------------------
SQL code

--try
select substring(通讯地址,1,13),min(substring(通讯地址,15,3)) from A group by substring(通讯地址,1,13)

------解决方案--------------------
SQL code

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
*/

--也可以用逗号做标记分割

------解决方案--------------------
SQL code

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