create table #t3(a varchar(10),b varchar(10),c int)
go
insert #t3 values('a1','b1','1')
insert #t3 values('a1','b2','2')
insert #t3 values('a2','b3','3')
insert #t3 values('a2','b4','4')
go
select --?? plz use one select/*
a1 b2 2
a2 b4 4
*/大大们帮忙了,sql2005
FROM #T3 AS A
WHERE NOT EXISTS(SELECT * FROM #t3
WHERE a = A.a AND c > A.c)
create table #t3(a varchar(10),b varchar(10),c int)
go
insert #t3 values('a1','b1','1')
insert #t3 values('a1','b2','2')
insert #t3 values('a2','b3','3')
insert #t3 values('a2','b4','4')
go
select * from #t3 t
where not exists(select * from #t3 where a=t.a and c>t.c)
a b c
---------- ---------- -----------
a1 b2 2
a2 b4 4(2 行受影响)
drop table #t3
--try
select * from #t3 where c%2 =0
from #t3 t
where c=(select top 1 c from #t3 where a=t.a and b=t.b order by c desc)
GO
create table #t3(a varchar(10),b varchar(10),c int)
go
insert #t3 values('a1','b1','1')
insert #t3 values('a1','b2','2')
insert #t3 values('a2','b3','3')
insert #t3 values('a2','b4','4')
go
SELECT * FROM #T3 T WHERE NOT EXISTS(SELECT 1 FROM #T3 WHERE A=T.A AND C>T.C)/*
a1 b2 2
a2 b4 4
*/
select * from #t3 t1
where
not exists(select 1 from #t3 t2 where t1.a=t2.a and t1.c<t2.c)