select * from tb t where not exists(select 1 from tb where fid=t.fid and [_index]>t.[_index]
select * from tb t where not exists(select * from tb where id=t.id and _index>t._index)
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-09-15 10:48:37 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[fid] varchar(3),[_index] int,[content] varchar(3)) insert [tb] select 1,'001',1,'aaa' union all select 2,'001',2,'bbb' union all select 3,'003',1,'ccc' union all select 4,'002',1,'ddd' union all select 5,'003',2,'eee' union all select 6,'003',3,'ggg' union all select 7,'002',2,'hhh' --------------开始查询-------------------------- select * from tb t where not exists(select 1 from tb where fid=t.fid and [_index]>t.[_index])----------------结果---------------------------- /*id fid _index content ----------- ---- ----------- ------- 2 001 2 bbb 6 003 3 ggg 7 002 2 hhh(3 行受影响)
*/
if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[fid] varchar(3),[_index] int,[content] varchar(3)) insert [tb] select 1,'001',1,'aaa' union all select 2,'001',2,'bbb' union all select 3,'003',1,'ccc' union all select 4,'002',1,'ddd' union all select 5,'003',2,'eee' union all select 6,'003',3,'ggg' union all select 7,'002',2,'hhh' --------------开始查询-------------------------- select * from tb t where not exists(select 1 from tb where fid=t.fid and [_index]>t.[_index]) --相同fid取INDEX最大select * from tb t,(select fid,max(_index) AS _index FROM TB group by fid) b where t.fid=b.fid and t._index=b._index select * from tb t where not exists(select 1 from tb where fid=t.fid and [_index]<t.[_index]) --相同FID取INDEX最小select * from tb t,(select fid,min(_index) AS _index FROM TB group by fid) b where t.fid=b.fid and t._index=b._index还有很多方法!我最喜欢这两种!
create table a(id int,fid varchar(12),_index int,content varchar(16)) insert into a select '1','001','1','aaa' union all select '2','001','2','bbb' union all select '3','003','1','ccc' union all select '4','002','1','ddd' union all select '5','003','2','eee' union all select '6','003','3','ggg' union all select '7','002','2','hhh'select a.id,a.fid,a._index,a.content from a,(select fid,max(_index) c from a group by fid) b where a.fid=b.fid and a._index=b.c
select * from tb t
where not exists(select * from tb where id=t.id and _index>t._index)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-15 10:48:37
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[fid] varchar(3),[_index] int,[content] varchar(3))
insert [tb]
select 1,'001',1,'aaa' union all
select 2,'001',2,'bbb' union all
select 3,'003',1,'ccc' union all
select 4,'002',1,'ddd' union all
select 5,'003',2,'eee' union all
select 6,'003',3,'ggg' union all
select 7,'002',2,'hhh'
--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where fid=t.fid and [_index]>t.[_index])----------------结果----------------------------
/*id fid _index content
----------- ---- ----------- -------
2 001 2 bbb
6 003 3 ggg
7 002 2 hhh(3 行受影响)
*/
go
create table [tb]([id] int,[fid] varchar(3),[_index] int,[content] varchar(3))
insert [tb]
select 1,'001',1,'aaa' union all
select 2,'001',2,'bbb' union all
select 3,'003',1,'ccc' union all
select 4,'002',1,'ddd' union all
select 5,'003',2,'eee' union all
select 6,'003',3,'ggg' union all
select 7,'002',2,'hhh'
--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where fid=t.fid and [_index]>t.[_index]) --相同fid取INDEX最大select * from tb t,(select fid,max(_index) AS _index FROM TB group by fid) b
where t.fid=b.fid and t._index=b._index
select * from tb t where not exists(select 1 from tb where fid=t.fid and [_index]<t.[_index]) --相同FID取INDEX最小select * from tb t,(select fid,min(_index) AS _index FROM TB group by fid) b
where t.fid=b.fid and t._index=b._index还有很多方法!我最喜欢这两种!
insert into a
select '1','001','1','aaa' union all
select '2','001','2','bbb' union all
select '3','003','1','ccc' union all
select '4','002','1','ddd' union all
select '5','003','2','eee' union all
select '6','003','3','ggg' union all
select '7','002','2','hhh'select a.id,a.fid,a._index,a.content from a,(select fid,max(_index) c from a group by fid) b where a.fid=b.fid and a._index=b.c