create table tb (From_name nvarchar(10),send_time datetime)insert into tb select '张三' ,'2011-09-11' union all select '张三' ,'2011-09-12' union all select '张三' ,'2011-09-13' union all select '李四' ,'2011-09-10' union all select '李四' ,'2011-09-14' union all select '王五' ,'2011-09-01' ---获取最小时间 ;with cte as ( select *,rowNum=ROW_NUMBER() over(partition by From_name order by send_time) from tb )select * from cte where rowNum=1---获取最大时间 ;with cte as ( select *,rowNum=ROW_NUMBER() over(partition by From_name order by send_time desc) from tb )select * from cte where rowNum=1
select * from tb a where not exists( select 1 from tb where From_name=a.From_name and send_time>a.send_time )
每个会员会有多条记录,楼主想要send_time最大或者最小的记录吗?
abc002 2011/1/1 我发送的第一条消息
abc001 2011/1/1 我发送的第2条消息
abc001 2011/1/1 我发送的第3条消息
abc002 2011/1/2 我发送的第2条消息
想要得出的结果:From_name send_time send_txtabc002 2011/1/2 我发送的第2条消息
abc001 2011/1/1 我发送的第3条消息
(From_name nvarchar(10),send_time datetime)insert into tb
select '张三' ,'2011-09-11' union all
select '张三' ,'2011-09-12' union all
select '张三' ,'2011-09-13' union all
select '李四' ,'2011-09-10' union all
select '李四' ,'2011-09-14' union all
select '王五' ,'2011-09-01'
---获取最小时间
;with cte as (
select *,rowNum=ROW_NUMBER() over(partition by From_name order by send_time) from tb
)select * from cte where rowNum=1---获取最大时间
;with cte as (
select *,rowNum=ROW_NUMBER() over(partition by From_name order by send_time desc) from tb
)select * from cte where rowNum=1
select * from tb a where not exists(
select 1 from tb where From_name=a.From_name and send_time>a.send_time
)