--建立测试环境 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) */
--上面少东西 select b.a_1 from (select left(a_1,4) c1,min(a_3) c2 from a group by left(a_1,4)) a inner join a b on a.c1=b.a_1 and a.c2=b.a_3
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)
select a.a_1 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 )
select t.a_1,t.a_3 from #tb t where convert(varchar(8),t.a_3,112)=(select MIN(convert(varchar(8),a_3,112)) from #tb tt where left(t.a_1,4) = left(a_1,4) group by left(tt.a_1,4))
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)
*/
select b.a_1 from
(select left(a_1,4) c1,min(a_3) c2 from a group by left(a_1,4)) a inner join
a b on a.c1=b.a_1 and a.c2=b.a_3
(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)
where not exists(select 1 from t where left(a_1,4)=left(a.a_1,4)and a_3<a.a_3 )
where convert(varchar(8),t.a_3,112)=(select MIN(convert(varchar(8),a_3,112))
from #tb tt where left(t.a_1,4) = left(a_1,4) group by left(tt.a_1,4))