大侠现身啊!
现在我有一个表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)
