数据表结构如下:
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
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
解决方案 »
- 如何对有重复字段(不是重复记录,一行中只有1个字段不一样)的记录统计条数?
- win7装sql 2005问题 com+目录警告 求助
- 如何读取不同服务器MSSQL数据库里的内容.
- 我怎么安装不了SQLServer2000?????
- 请教SQL远程连接问题
- SQL语句:如何实现两个表的无条件列连接?刚才没有搜到
- 初级问题,如何在win2000pfs专业版上安装SQL SERVER2000企业版或标准版???
- 寻求基于asp+sql server的bbs源代码,麻烦给位了!急~~~~~~~~~~~~~~~~
- access查询的数据类型转换问题:
- Sql server 2012搭建DNS的时候报4014事件,希望大神帮助,急,谢谢!
- 求《Microsoft SQL Server 2000 DTS Step by Step》的光盘
- "用户"和"角色"的概念是不是类似于 windows 用户管理中的"用户名"和"组"呀?是不是互相类似呀,有区别,区别在哪里呢?
select * from t where time = (select max(time) from t group by name )
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)
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())
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 @tselect [name],max([time]) from @t group by [name]
--你应该有标识列吧
--把每条记录按照标识列降序取第一条
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)