发帖求助：关于查询

[super_miker]

有一个表，有3个列：ID、Name、Time
其中ID是Int类型，Name是Varchar类型，Time是DateTime类型

现要查询：对于每个ID，求其Time列最大值所对应的Name，语句该如何写？

先谢过大家

[wanggang_sql]

没看到你要得到的查询结果，请说清楚

[哥哥大]

加入你的数据库名为 aa,表名为cs,SQL如下： use aa select name from cs where time in (select max(time) from cs)

[哥哥大]

如果ID值重复，并每个ID都有大小值的time,sql如下： use aa select name from cs where time in (select max(time) from cs group by id) 我的语句是最简单的。

[a320898]

好共同认识他突然很听话

[骑着蜗牛去爬山]

select name from (select *,row=row_number()over(partition by id order by time desc) from tb)t where row=1

[xiaoqi7456]

select * from tb a where not exists (select 1 from tb b where a.id=b.id and b.time>a.time)

[發糞塗牆]

SELECT  *
FROM    TB a
WHERE   EXISTS ( SELECT 1
                 FROM   ( SELECT    id ,
                                    MAX([time]) [time]
                          FROM      TB
                          GROUP BY  id
                        ) b
                 WHERE  a.id = b.id
                        AND a.[time] = b.[time] )

[szm341]

select * from tb a where exists (select 1 from tb where a.id=id
group by id having max(time)=a.time) 

[belone99]

select id,max(name),max(time) from table group by id

[波西米亚人生]

select id,name from MYTABLE a where exists (select 1 from MYTABLE where a.id=id group by id having max(time)=a.time)

[FelixOu]

select [ID],[Name],[Time] from Table a inner join ( select [ID],MAX([Time]) from Table b group by [ID] ) c where a.[ID]= c.[ID] and a.[Time] = c.[Time]

[曲军昌]

select id,name from 表 where time=max(time) group by id

[浪花三朵朵]

select t2.Name from Test t2 join (select ID,MAX(Time) time from Test t group by ID) t1 on t2.ID=t1.ID and t2.Time=t1.time

[找猫画虎]

select name from (select *,row=row_number()over(partition by id order by time desc) from tb)t where row=1 写的很简介呀！

[-晴天]

select * from tb a where not exists(select 1 from tb where id=a.id and time>a.time)

[menggang9801]

引用 8 楼 lemongrassstaste 的回复:

select [ID],[Name],[Time] from Table a inner join ( select [ID],MAX([Time]) from Table b group by [ID] ) c where a.[ID]= c.[ID] and a.[Time] = c.[Time]

我写的也是这个