取出来的 数据 应该是 ID H T V 2 1 1 2 1 3 2 1 2 3 3 3 2 2 1 4
select * FROM table as T left outer jion (select Max(a.id) as id ,a.h,a.t froM table as a where (select * FROM table as b where a.h=b.h and a.t=b.t and a.id<>b.id ) group by a.h,a.t) as C where T.h = c.h and T.T = C.T
select * FROM table as T left outer join (select Max(a.id) as id ,a.h,a.t froM table as a where (select * FROM table as b where a.h=b.h and a.t=b.t and a.id<>b.id ) group by a.h,a.t) as C on T.h = c.h and T.T = C.T
if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] int,[H] int,[T] int,[V] int) insert [tb] select 1,1,1,1 union all select 2,1,1,2 union all select 1,2,1,2 union all select 1,3,2,1 union all select 2,3,3,3 union all select 2,2,1,4 goselect * from tb t where not exists(select 1 from tb where h=t.h and t=t.t and id>t.id) /** ID H T V ----------- ----------- ----------- ----------- 2 1 1 2 1 3 2 1 2 3 3 3 2 2 1 4(4 行受影响) **/
--方法二 select * from tb t where id=(select max(id) from tb where h=t.h and t=t.t) /** ID H T V ----------- ----------- ----------- ----------- 2 1 1 2 1 3 2 1 2 3 3 3 2 2 1 4(4 行受影响) **/--方法三 select * from tb t where id=(select top 1 id from tb where h=t.h and t=t.t order by id desc) /** ID H T V ----------- ----------- ----------- ----------- 2 1 1 2 1 3 2 1 2 3 3 3 2 2 1 4(4 行受影响) **/--方法四 select a.* from tb a join (select max(id) as id,h,t from tb group by h,t) b on a.h=b.h and a.t=b.t and a.id=b.id /** ID H T V ----------- ----------- ----------- ----------- 2 1 1 2 1 3 2 1 2 3 3 3 2 2 1 4(4 行受影响) **/
if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] int,[H] int,[T] int,[V] int) insert [tb] select 1,1,1,1 union all select 2,1,1,2 union all select 1,2,1,2 union all select 1,3,2,1 union all select 2,3,3,3 union all select 2,2,1,4 go 这个也要写吗?
ID H T V
2 1 1 2
1 3 2 1
2 3 3 3
2 2 1 4
left outer jion
(select Max(a.id) as id ,a.h,a.t froM table as a
where (select * FROM table as b where a.h=b.h and a.t=b.t and a.id<>b.id )
group by a.h,a.t) as C
where T.h = c.h and T.T = C.T
left outer join
(select Max(a.id) as id ,a.h,a.t froM table as a
where (select * FROM table as b where a.h=b.h and a.t=b.t and a.id<>b.id )
group by a.h,a.t) as C on T.h = c.h and T.T = C.T
go
create table [tb]([ID] int,[H] int,[T] int,[V] int)
insert [tb]
select 1,1,1,1 union all
select 2,1,1,2 union all
select 1,2,1,2 union all
select 1,3,2,1 union all
select 2,3,3,3 union all
select 2,2,1,4
goselect * from tb t
where not exists(select 1 from tb where h=t.h and t=t.t and id>t.id)
/**
ID H T V
----------- ----------- ----------- -----------
2 1 1 2
1 3 2 1
2 3 3 3
2 2 1 4(4 行受影响)
**/
select * from tb t
where id=(select max(id) from tb where h=t.h and t=t.t)
/**
ID H T V
----------- ----------- ----------- -----------
2 1 1 2
1 3 2 1
2 3 3 3
2 2 1 4(4 行受影响)
**/--方法三
select * from tb t
where id=(select top 1 id from tb where h=t.h and t=t.t order by id desc)
/**
ID H T V
----------- ----------- ----------- -----------
2 1 1 2
1 3 2 1
2 3 3 3
2 2 1 4(4 行受影响)
**/--方法四
select a.* from tb a
join (select max(id) as id,h,t from tb group by h,t) b
on a.h=b.h and a.t=b.t and a.id=b.id
/**
ID H T V
----------- ----------- ----------- -----------
2 1 1 2
1 3 2 1
2 3 3 3
2 2 1 4(4 行受影响)
**/
go
create table [tb]([ID] int,[H] int,[T] int,[V] int)
insert [tb]
select 1,1,1,1 union all
select 2,1,1,2 union all
select 1,2,1,2 union all
select 1,3,2,1 union all
select 2,3,3,3 union all
select 2,2,1,4
go
这个也要写吗?
这个是根据你给的测试数据建立的测试表和插入数据的语句,查询的时候不需要写,只需要写下面的select开始的查询语句即可