drop table tbtest
go
create table tbtest([User] varchar(10),GreatTime datetime,AutoId int,Title varchar(20))
insert into tbtest
select '小张','2006-06-30 18:00',16,'贴子名'
union all select '小张','2006-06-30 18:10',17,'贴子名'
union all select '小张','2006-06-30 18:15',15,'贴子名'
union all select '小王','2006-06-30 17:00',18,'贴子名'
union all select '小王','2006-06-30 18:00',20,'贴子名'
union all select '小李','2006-06-30 19:00',12,'贴子名'
union all select '小李','2006-06-30 18:00',22,'贴子名'
union all select '小李','2006-06-30 18:10',55,'贴子名'
union all select '小李','2006-06-30 18:50',55,'贴子名'
select * from tbtest
where GreatTime=(select max(GreatTime) from tbtest a where a.[User]=tbtest.[User])
/*
User GreatTime AutoId Title
---------- ------------------------------------------------------ ----------- --------------------
小张 2006-06-30 18:15:00.000 15 贴子名
小王 2006-06-30 18:00:00.000 20 贴子名
小李 2006-06-30 19:00:00.000 12 贴子名(所影响的行数为 3 行)
*/
go
create table tbtest([User] varchar(10),GreatTime datetime,AutoId int,Title varchar(20))
insert into tbtest
select '小张','2006-06-30 18:00',16,'贴子名'
union all select '小张','2006-06-30 18:10',17,'贴子名'
union all select '小张','2006-06-30 18:15',15,'贴子名'
union all select '小王','2006-06-30 17:00',18,'贴子名'
union all select '小王','2006-06-30 18:00',20,'贴子名'
union all select '小李','2006-06-30 19:00',12,'贴子名'
union all select '小李','2006-06-30 18:00',22,'贴子名'
union all select '小李','2006-06-30 18:10',55,'贴子名'
union all select '小李','2006-06-30 18:50',55,'贴子名'
select * from tbtest
where GreatTime=(select max(GreatTime) from tbtest a where a.[User]=tbtest.[User])
/*
User GreatTime AutoId Title
---------- ------------------------------------------------------ ----------- --------------------
小张 2006-06-30 18:15:00.000 15 贴子名
小王 2006-06-30 18:00:00.000 20 贴子名
小李 2006-06-30 19:00:00.000 12 贴子名(所影响的行数为 3 行)
*/
解决方案 »
- 数据库查询到excel!!!!!!!!!!!!!!!!!!!
- 对比两张表的数量
- 新手问题,高手帮帮忙
- 在SQL SERVER2000上建立的ASP程序,移植到MySQL上,需要改动吗?哪里可以下载MySQL呢?
- sqlsever2000在不同Sql服务器中如何做插入数据?也就是本机一个服务器pcSqserver1,链接另一个服务器pcsqserver2
- 触发器和存储过程
- 求关于asp.net或SQL或C#的英文译文,提供者都给分,谢谢
- -------xp_sendmail各參數大小
- 交叉表问题!在线等急呀!!!!
- 分类汇总的问题
- 数据恢复,如何只恢复某个表的数据?
- 如何写sql,让select出的数据带着序号???
from tbtest a
where a.greattime in(select greatTime
from (select [User],max(greatTime) as greatTime from tbtest group by [user]) b)
select *
from tbtest a
where [user]+convert(char(16),a.greattime,126)
in(select [user]+convert(char(16),greattime,126) as greattime
from (select [User],max(convert(char(16),greattime,126)) as greatTime from tbtest group by [user]) b)
where not exists(select 1 from tbl where a.user=user and a.Greattime<greattime)
insert into tbtest
select '小张','2006-06-30 18:00',16,'贴子名'
union all select '小张','2006-06-30 18:10',17,'贴子名'
union all select '小张','2006-06-30 18:15',15,'贴子名'
union all select '小王','2006-06-30 17:00',18,'贴子名'
union all select '小王','2006-06-30 18:00',20,'贴子名'
union all select '小李','2006-06-30 19:00',12,'贴子名'
union all select '小李','2006-06-30 18:00',22,'贴子名'
union all select '小李','2006-06-30 18:10',55,'贴子名'
union all select '小李','2006-06-30 18:50',55,'贴子名'select * from tbtest a
where not exists(select 1 from tbtest b where a.[user]=b.[user] and a.greatTime<b.greattime)
User GreatTime AutoId Title
---------- ----------------------- ----------- --------------------
小张 2006-06-30 18:15:00.000 15 贴子名
小王 2006-06-30 18:00:00.000 20 贴子名
小李 2006-06-30 19:00:00.000 12 贴子名(3 行受影响)
SELECT A AS,B AS,C AS,D AS FROM **** WHERE ****='???'
SELECT A AS User,B AS GreatTime,C AS AutoId,D AS Title FROM **** WHERE ****='???'