逻辑:按name分区,按time降序排序,然后去第一条记录: Select name,time,status from ( select row_number() over(partition by name order by time desc) as RN ,name,time,status from TableA ) as t where t.RN=1
SELECT name,MAX(time)time,MIN(status)status FROM TB GROUP BY name可以GROUP BY 类似这样
select top 1 name,time,status from table_name group by name order by time desc
with cte as( select *,row_number() over(partition by name order by time desc) as cnt from tb_name ) select * from cte where cnt=1
with a(name,time,status) as (select '张三','2015-1-15 13:00:01',1 union all select '张三','2015-1-15 15:00:01',0 union all select '张三','2015-1-15 14:00:01',1 union all select '张三','2015-1-15 12:00:01',1 union all select '李四','2015-1-15 11:00:01',1 union all select '李四','2015-1-15 10:00:01',0 union all select '李四','2015-1-15 10:00:01',0) select c.name,c.time,c.status from (select ROW_NUMBER()over(partition by name order by time desc) as b,* from a) c where c.B=1
Select name,time,status
from (
select row_number() over(partition by name order by time desc) as RN
,name,time,status
from TableA
) as t
where t.RN=1
FROM TB
GROUP BY name可以GROUP BY 类似这样
select top 1 name,time,status from table_name
group by name
order by time desc
select *,row_number() over(partition by name order by time desc) as cnt from tb_name
)
select * from cte where cnt=1
(select '张三','2015-1-15 13:00:01',1 union all
select '张三','2015-1-15 15:00:01',0 union all
select '张三','2015-1-15 14:00:01',1 union all
select '张三','2015-1-15 12:00:01',1 union all
select '李四','2015-1-15 11:00:01',1 union all
select '李四','2015-1-15 10:00:01',0 union all
select '李四','2015-1-15 10:00:01',0)
select c.name,c.time,c.status from
(select ROW_NUMBER()over(partition by name order by time desc) as b,* from a) c
where c.B=1