求Sql语句

zhouxm0521 2007-12-09 10:13:18
数据表结构如下:
name,time
实际数据:
Name1,2007-12-8 5:00:45
Name2,2007-12-8 5:03:34
Name1,2007-12-9 12:00:23
Name2,2007-12-7 11:09:54
要求从数据表中提取每个名称的最后一条记录,该如何写Sql?也即是结果应该提出两条记录,分别为:
Name2,2007-12-8 5:03:34
Name1,2007-12-9 12:00:23
...全文
43 11 打赏 收藏 举报
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wzy_love_sly 2007-12-09
select top 1 * from @t a where time=(select top 1 time from @t where a.name = name)
  • 打赏
  • 举报
回复
晓风残月0110 2007-12-09

--你应该有标识列吧
--把每条记录按照标识列降序取第一条
declare @t table(ID int,name varchar(10),time datetime)
insert into @t select 1,'name1','2007-12-8 5:00:45'
insert into @t select 2,'name1','2007-12-9 5:00:50'
insert into @t select 3,'name2','2007-12-8 5:00:45'
insert into @t select 4,'name2','2007-12-9 5:00:45'

--
select * from @t a where time=(select top 1 time from @t where a.name = name order by ID desc)
  • 打赏
  • 举报
回复
zhouxm0521 2007-12-09
已经搞定,谢谢大家!
  • 打赏
  • 举报
回复
JL99000 2007-12-09
declare @t table([name] varchar(10),[time] datetime)
insert into @t select 'name1','2007-12-8 5:00:45'
insert into @t select 'name1','2007-12-8 5:00:50'
insert into @t select 'name2','2007-12-8 5:00:45'
insert into @t select 'name2','2007-12-9 5:00:45'
select * from @t

select [name],max([time]) from @t group by [name]
  • 打赏
  • 举报
回复
zhouxm0521 2007-12-09
charry0110你好,,这个语句可能会有一个问题,如果有两条记录的时间恰好相同,但Name不同,则提出的记录条数就多了,我想要的效果是就提取每个不同Name的最后一条记录。
  • 打赏
  • 举报
回复
晓风残月0110 2007-12-09

declare @t table(name varchar(10),time datetime)
insert into @t select 'name1','2007-12-8 5:00:45'
insert into @t select 'name1','2007-12-8 5:00:50'
insert into @t select 'name2','2007-12-8 5:00:45'
insert into @t select 'name2','2007-12-9 5:00:45'
--取最大
select * from @t a where time=(select max(time) from @t where a.name = name)
--取最小
select * from @t a where time=(select min(time) from @t where a.name = name)
--按记录顺序取第一条
select * from @t a where time=(select top 1 time from @t where a.name = name)
----随机取
select * from @t a where time=(select top 1 time from @t where a.name = name order by newid())
  • 打赏
  • 举报
回复
晓风残月0110 2007-12-09

declare @t table(name varchar(10),time datetime)
insert into @t select 'name1','2007-12-8 5:00:45'
insert into @t select 'name1','2007-12-8 5:00:50'
insert into @t select 'name2','2007-12-8 5:00:45'
insert into @t select 'name2','2007-12-9 5:00:45'
select * from @t a where time=(select max(time) from @t where a.name = name)
  • 打赏
  • 举报
回复
zhouxm0521 2007-12-09
select name,Max(time) as time from v_t110 group by wellname
  • 打赏
  • 举报
回复
晓风残月0110 2007-12-09

select * from t where time = (select max(time) from t group by name )
  • 打赏
  • 举报
回复
zhouxm0521 2007-12-09
谢谢love985 ,不过这应该不行。
  • 打赏
  • 举报
回复
love985 2007-12-09
select max(time),name from t group by name,time
  • 打赏
  • 举报
回复
发帖
MS-SQL Server

3.4w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2007-12-09 10:13
社区公告
暂无公告