表A:F1 F2 F3 F4 F5
AAA CarA 2010-3-1 2010-3-31 2.2
AAA CarA 2010-4-1 2010-4-30 2.0
BBB BikeB 2010-3-1 2010-3-31 1.0要求根据F1列的内容,找出F3时间最早的那条记录。
如上述例子的结果应该为:F1 F2 F3 F4 F5
AAA CarA 2010-3-1 2010-3-31 2.2
BBB BikeB 2010-3-1 2010-3-31 1.0
AAA CarA 2010-3-1 2010-3-31 2.2
AAA CarA 2010-4-1 2010-4-30 2.0
BBB BikeB 2010-3-1 2010-3-31 1.0要求根据F1列的内容,找出F3时间最早的那条记录。
如上述例子的结果应该为:F1 F2 F3 F4 F5
AAA CarA 2010-3-1 2010-3-31 2.2
BBB BikeB 2010-3-1 2010-3-31 1.0
SELECT * FROM A
INNER JOIN
(
select F1,MIN(F3 ) F3
from A
GROUP BY F1
) M
WHERE A.F1=M.F1 ANDA.F3=M.F3
INSERT INTO @test(f1,f2,f3,f4,f5)
select 'AAA','CarA','2010-3-1','2010-3-31',2.2 union all
select 'AAA','CarA','2010-4-1','2010-4-30',2.0 union all
select 'BBB','BikeB','2010-3-1','2010-3-31',1.0SELECT * FROM @test AS t1 WHERE NOT EXISTS (SELECT 1 FROM @test AS t2 WHERE t1.f1 = t2.f1 AND t1.f3 > t2.f3)
/*
f1 f2 f3 f4 f5
-------------------- -------------------- ------------------------------------------------------ ------------------------------------------------------ -----------------------------------------------------
AAA CarA 2010-03-01 00:00:00.000 2010-03-31 00:00:00.000 2.2000000000000002
BBB BikeB 2010-03-01 00:00:00.000 2010-03-31 00:00:00.000
*/
select * from a where f3=(SELECT min(f3)
FROM [test].[dbo].[a])
where not exists(select 1 from a where f1=t.f1 and f<t3)
where not exists(select 1 from a where f1=t.f1 and f3<t3)
select * from a where exists(select min(f3) from a)
select t.* from a t where f3 = (select min(f3) from a where f1 = t.f1)select t.* from a t where not exists (select 1 from a where f1 = t.f1 and f3 < t.f3)select a.* from tb a inner join (select f1,min(f3) as minf3 from tb group by f1) b on a.f1=b.f1 and a.f3=b.minf3