SQL语句获取每个用户的最新信息的问题，在线等，送分~

[fivestars]

用户名 文件时间 文件大小
1 aa 2010-1-17 10:40 0.1MB
2 cc 2010-1-18 12:40 0.3MB
3 aa 2010-1-18 10:40 0.7MB
4 aa 2010-1-17 20:40 0.1MB
5 cc 2010-1-17 10:40 0.1MB
6 bb 2010-1-17 10:40 0.1MB

怎样写sql语句获取每个用户时间最新的那一条信息，请写出SQL代码，谢谢了

[fivestars]

呵呵，谢谢大家了，回者有份，结贴给分～

[--小F--]

select
  *
from
  tb t
where
  文件时间=(select max(文件时间) from tb where 用户名=t.用户名)

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2010-01-19 11:54:25
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[用户名] varchar(2),[文件时间] datetime,[文件大小] varchar(5))
insert [tb]
select 1,'aa','2010-1-17 10:40','0.1MB' union all
select 2,'cc','2010-1-18 12:40','0.3MB' union all
select 3,'aa','2010-1-18 10:40','0.7MB' union all
select 4,'aa','2010-1-17 20:40','0.1MB' union all
select 5,'cc','2010-1-17 10:40','0.1MB' union all
select 6,'bb','2010-1-17 10:40','0.1MB'
--------------开始查询--------------------------
select
  *
from
  tb t
where
  文件时间=(select max(文件时间) from tb where 用户名=t.用户名)
----------------结果----------------------------
/* id          用户名  文件时间                    文件大小
----------- ---- ----------------------- -----
2           cc   2010-01-18 12:40:00.000 0.3MB
6           bb   2010-01-17 10:40:00.000 0.1MB
3           aa   2010-01-18 10:40:00.000 0.7MB

(3 行受影响)

*/

[Mr_Nice]

--> 测试数据：[TESTTB]
if object_id('[TESTTB]') is not null drop table [TESTTB]
create table [TESTTB]([ID] int,[用户名] varchar(2),[文件时间] datetime,[文件大小] varchar(5))
insert [TESTTB]
select 1,'aa','2010-1-17 10:40','0.1MB' union all
select 2,'cc','2010-1-18 12:40','0.3MB' union all
select 3,'aa','2010-1-18 10:40','0.7MB' union all
select 4,'aa','2010-1-17 20:40','0.1MB' union all
select 5,'cc','2010-1-17 10:40','0.1MB' union all
select 6,'bb','2010-1-17 10:40','0.1MB'

select * from [TESTTB]


select *
from [TESTTB] TB
where 文件时间=(select max(文件时间) from [TESTTB] where 用户名=TB.用户名)



--结果
/*

2 cc 2010-01-18 12:40:00.000 0.3MB
6 bb 2010-01-17 10:40:00.000 0.1MB
3 aa 2010-01-18 10:40:00.000 0.7MB
*/

[nianran520]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[用户名] varchar(2),[文件时间] datetime,[文件大小] varchar(5))
insert [tb]
select 1,'aa','2010-1-17 10:40','0.1MB' union all
select 2,'cc','2010-1-18 12:40','0.3MB' union all
select 3,'aa','2010-1-18 10:40','0.7MB' union all
select 4,'aa','2010-1-17 20:40','0.1MB' union all
select 5,'cc','2010-1-17 10:40','0.1MB' union all
select 6,'bb','2010-1-17 10:40','0.1MB'

select * from [tb] t
where [文件时间]=(select max([文件时间]) from [tb] where [用户名]=t.[用户名])
------------------------
2	cc	2010-01-18 12:40:00.000	0.3MB
6	bb	2010-01-17 10:40:00.000	0.1MB
3	aa	2010-01-18 10:40:00.000	0.7MB

[Mr_Nice]

[Quote=引用 2 楼 fredrickhu 的回复:]
SQL code----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2010-01-19 11:54:25
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00?-
[/Quote]


好快啊！

[nianran520]

-or
select * from [tb] t
where not exists (select 1 from [tb] where [用户名]=t.[用户名] and [文件时间] > t.[文件时间])

[zhangle861010]

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[用户名] varchar(2),[文件时间] datetime,[文件大小] varchar(5))
insert [tb]
select 1,'aa','2010-1-17 10:40','0.1MB' union all
select 2,'cc','2010-1-18 12:40','0.3MB' union all
select 3,'aa','2010-1-18 10:40','0.7MB' union all
select 4,'aa','2010-1-17 20:40','0.1MB' union all
select 5,'cc','2010-1-17 10:40','0.1MB' union all
select 6,'bb','2010-1-17 10:40','0.1MB'


select * from tb t where not exists (select * from tb where 文件时间>t.文件时间 and 用户名=t.用户名)