关于查询 急~~~~~

[heqiang133]

code st_date name tsl
1001 2003-12-31 kkk 0.04
1001 2005-12-10 kkk 0.03
1001 2006-12-1 kkk 0.03
10030 2004-1-1 ccc 0.03
10030 2005-1-12 ccc 0.02
1002 2004-1-10 zzz 0.01

用SQL语句查出下边的数据

1001 2006-12-1 kkk 0.03
10030 2005-1-12 ccc 0.02
1002 2004-1-10 zzz 0.01

方法越简练越好

[heqiang133]

没人会吗？

[-狙击手-]

select *
from table a
where not exists(select 1 from table where code = a.code and st_date < a.st_date)

[liangCK]

create table tb
(
   code int,
   st_date datetime,
   name varchar(10),
   tsl decimal(10,2)
)
insert tb select 1001,   '2003-12-31'   ,'kkk',   0.04   
insert tb select 1001 ,  '2005-12-10'   ,'kkk' ,  0.03   
insert tb select 1001  , '2006-12-1'   ,'kkk'   ,0.03   
insert tb select 10030  , '2004-1-1'  , 'ccc'   ,0.03   
insert tb select 10030   ,'2005-1-12',   'ccc'   ,0.02   
insert tb select 1002   ,'2004-1-10',   'zzz'   ,0.01

select code,min(st_date) st_date,name,cast(min(tsl) as varchar) tsl 
from tb 
group by code,name
order by tsl desc

drop table tb

/*
code        st_date                    name       tsl                            
----------- ------------------------- ---------- ------------------------------ 
1001        2003-12-31 00:00:00.000      kkk        0.03
10030       2004-01-01 00:00:00.000      ccc        0.02
1002        2004-01-10 00:00:00.000      zzz        0.01

（所影响的行数为 3 行）
*/

[-狙击手-]

declare @t table(code varchar(6),st_date datetime,[name] varchar(4),tsl numeric(12,2))
insert @t select '1001','2003-12-31','kkk',0.04   
insert @t select '1001','2005-12-10','kkk',0.03   
insert @t select '1001','2006-12-1','kkk',0.03   
insert @t select '10030','2004-1-1','ccc',0.03   
insert @t select '10030','2005-1-12','ccc',0.02   
insert @t select '1002','2004-1-10','zzz',0.01 


select *
from @t a
where not exists(select 1 from @t where code = a.code and st_date > a.st_date)
/*


code   st_date                                                name tsl            
------ ------------------------------------------------------ ---- -------------- 
1001   2006-12-01 00:00:00.000                                kkk  .03
10030  2005-01-12 00:00:00.000                                ccc  .02
1002   2004-01-10 00:00:00.000                                zzz  .01

（所影响的行数为 3 行）

*/

[Limpire]

select *
from table a
where not exists(select 1 from table where code = a.code and st_date > a.st_date)

[中国风]

select * from t a where st_date=(select max(st_date) from t where code=a.code)

[cxmcxm]

select * from 表名 a where not exists(select * from 表名 whree code=a.code and st_date>a.st_date)

[wzy_love_sly]

select * from tb a where not exists
(select 1 from tb where code = a.code and st_date > a.st_date)