如果你的字符串是标准的,都是八位,即月,日是单位数的都补上0的话,则如下即可. create table tb(id int,sj varchar(10),name varchar(10)) insert into tb values(1 ,'19870609', '交大') insert into tb values(1 ,'19700605', 'XX中学') insert into tb values(2 ,'19990601', '一中') insert into tb values(3 ,'19980602', 'XX学院') insert into tb values(3 ,'19940601', 'XX高中') goselect t.* from tb t where sj = (select max(sj) from tb where id = t.id) order by t.id /* id sj name ----------- ---------- ---------- 1 19870609 交大 2 19990601 一中 3 19980602 XX学院(所影响的行数为 3 行) */select t.* from tb t where not exists (select 1 from tb where id = t.id and sj > t.sj) order by t.id /* id sj name ----------- ---------- ---------- 1 19870609 交大 2 19990601 一中 3 19980602 XX学院(所影响的行数为 3 行) */drop table tb
如果你的日期不标准,则转换SJ为datetime型然后再比较即可. create table tb(id int,sj varchar(10),name varchar(10)) insert into tb values(1 ,'19870609', '交大') insert into tb values(1 ,'19700605', 'XX中学') insert into tb values(2 ,'19990601', '一中') insert into tb values(3 ,'19980602', 'XX学院') insert into tb values(3 ,'19940601', 'XX高中') goselect t.* from tb t where cast(sj as datetime) = (select max(cast(sj as datetime)) from tb where id = t.id) order by t.id /* id sj name ----------- ---------- ---------- 1 19870609 交大 2 19990601 一中 3 19980602 XX学院(所影响的行数为 3 行) */select t.* from tb t where not exists (select 1 from tb where id = t.id and cast(sj as datetime) > cast(t.sj as datetime)) order by t.id /* id sj name ----------- ---------- ---------- 1 19870609 交大 2 19990601 一中 3 19980602 XX学院(所影响的行数为 3 行) */drop table tb
是SJ字段变成了字符型?? 如果是这样的话。 那就用covert()把它转换成日期型。。
create table tb(id int,sj varchar(10),name varchar(10))
insert into tb values(1 ,'19870609', '交大')
insert into tb values(1 ,'19700605', 'XX中学')
insert into tb values(2 ,'19990601', '一中')
insert into tb values(3 ,'19980602', 'XX学院')
insert into tb values(3 ,'19940601', 'XX高中')
goselect t.* from tb t where sj = (select max(sj) from tb where id = t.id) order by t.id
/*
id sj name
----------- ---------- ----------
1 19870609 交大
2 19990601 一中
3 19980602 XX学院(所影响的行数为 3 行)
*/select t.* from tb t where not exists (select 1 from tb where id = t.id and sj > t.sj) order by t.id
/*
id sj name
----------- ---------- ----------
1 19870609 交大
2 19990601 一中
3 19980602 XX学院(所影响的行数为 3 行)
*/drop table tb
create table tb(id int,sj varchar(10),name varchar(10))
insert into tb values(1 ,'19870609', '交大')
insert into tb values(1 ,'19700605', 'XX中学')
insert into tb values(2 ,'19990601', '一中')
insert into tb values(3 ,'19980602', 'XX学院')
insert into tb values(3 ,'19940601', 'XX高中')
goselect t.* from tb t where cast(sj as datetime) = (select max(cast(sj as datetime)) from tb where id = t.id) order by t.id
/*
id sj name
----------- ---------- ----------
1 19870609 交大
2 19990601 一中
3 19980602 XX学院(所影响的行数为 3 行)
*/select t.* from tb t where not exists (select 1 from tb where id = t.id and cast(sj as datetime) > cast(t.sj as datetime)) order by t.id
/*
id sj name
----------- ---------- ----------
1 19870609 交大
2 19990601 一中
3 19980602 XX学院(所影响的行数为 3 行)
*/drop table tb
比如‘asdasd’ ,‘efefef’,‘我的温情吧’
之类的