34,590
社区成员
发帖
与我相关
我的任务
分享
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
--方法二
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
select * 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 行受影响)
**/