大侠现身啊!
现在我有一个表a,表a及测试数据如下:
  a_1                   a_3
15150             2007-04-29
15151             2007-04-25
15155             2007-06-01
15160             2007-08-08
15162             2007-07-08
16150             2007-06-01
16153             2006-06-01
现在我想找出a_1字段中前4位相同,而a_3字段时间最早的那条记录的a_1字段值

谢谢了,在线等

------解决方案--------------------
--建立测试环境
create table #tb(a_1 varchar(10),a_3 datetime)
insert #tb(a_1,a_3)
select '15150 ', '2007-04-29 ' union all
select '15151 ', '2007-04-25 ' union all
select '15155 ', '2007-06-01 ' union all
select '15160 ', '2007-08-08 ' union all
select '15162 ', '2007-07-08 ' union all
select '16150 ', '2007-06-01 ' union all
select '16153 ', '2006-06-01 '
go
--执行测试语句
select t.a_1,t.a_3 from #tb t
where not exists(select 1 from #tb where left(t.a_1,4) = left(a_1,4) and t.a_3 > a_3)
go
--删除测试环境
drop table #tb
go
/*--测试结果
a_1 a_3
---------- ------------------------------------------------------
15151 2007-04-25 00:00:00.000
15162 2007-07-08 00:00:00.000
16153 2006-06-01 00:00:00.000

(3 row(s) affected)


*/


------解决方案--------------------
create table t
(a_1 varchar(10), a_3 varchar(10))
insert into t
select '15150 ', '2007-04-29 ' union all
select '15151 ', '2007-04-25 ' union all
select '15155 ', '2007-06-01 ' union all
select '15160 ', '2007-08-08 ' union all
select '15162 ', '2007-07-08 ' union all
select '16150 ', '2007-06-01 ' union all
select '16153 ', '2006-06-01 '


select * from t a
where not exists(select 1 from t where left(a_1,4)=left(a.a_1,4)and a_3 <a.a_3 )


a_1 a_3
---------- ----------
15151 2007-04-25
15162 2007-07-08
16153 2006-06-01

(3 row(s) affected)