我想取记录表中,最新时间的某一个字段的值,如
id score get_time
1 20 2010-1-1
1 50 2010-1-2
1 30 2010-1-3
2 40 2010-1-1
2 50 2010-1-2
取最新时间的score,结果是
id score get_time
1 30 2010-1-3
2 50 2010-1-2
一个sql 怎么写,感觉很简单,但是一下就想不出来
id score get_time
1 20 2010-1-1
1 50 2010-1-2
1 30 2010-1-3
2 40 2010-1-1
2 50 2010-1-2
取最新时间的score,结果是
id score get_time
1 30 2010-1-3
2 50 2010-1-2
一个sql 怎么写,感觉很简单,但是一下就想不出来
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([id] int,[score] int,[get_time] datetime)
insert [TB]
select 1,20,'2010-1-1' union all
select 1,50,'2010-1-2' union all
select 1,30,'2010-1-3' union all
select 2,40,'2010-1-1' union all
select 2,50,'2010-1-2'
GO--> 查询结果
SELECT * FROM [TB] t
where not exists (select * from TB where t.id=id and get_time>t.get_time)--> 删除表格
--DROP TABLE [TB]
insert into tb values(1 ,20 ,'2010-1-1')
insert into tb values(1 ,50 ,'2010-1-2')
insert into tb values(1 ,30 ,'2010-1-3')
insert into tb values(2 ,40 ,'2010-1-1')
insert into tb values(2 ,50 ,'2010-1-2')
goselect t.* from tb t where get_time = (select max(get_time) from tb where id = t.id) order by t.idselect t.* from tb t where not exists (select 1 from tb where id = t.id and get_time > t.get_time) order by t.id
drop table tb
/*
id score get_time
----------- ----------- ------------------------------------------------------
1 30 2010-01-03 00:00:00.000
2 50 2010-01-02 00:00:00.000(所影响的行数为 2 行)id score get_time
----------- ----------- ------------------------------------------------------
1 30 2010-01-03 00:00:00.000
2 50 2010-01-02 00:00:00.000(所影响的行数为 2 行)
*/