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

如何只取出一条规定条件相同取ID号大的数据
有一表比如 

ID H T V
1 1 1 1
2 1 1 2
1 2 1 2
1 3 2 1
2 3 3 3
2 2 1 4

如何通过SQL语句 取出数据 要求 如何 H 和 T 都相同 并且 ID 不同的情况下 只取出ID大的数据

------解决方案--------------------
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
------解决方案--------------------
SQL code
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 行受影响)
**/