导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求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
...全文
26 点赞 收藏 11
写回复
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告