select * from tab a
where not exists (
select 1 from tab
where Assign_Dept=a.Assign_Dept
and Assign_Date>a.Assign_Date
)
where not exists (
select 1 from tab
where Assign_Dept=a.Assign_Dept
and Assign_Date>a.Assign_Date
)
select * from tab a
where Assign_Date= (
select max(Assign_Date) from tab
where Assign_Dept=a.Assign_Dept
)
from yourTable a inner join (
select Assign_Dept,assign_Name,max(Assign_Date) as maxAssignDate
from yourTable
group by Assign_Dept,assign_Name
) b
on a.Assign_Dept=b.Assign_Dept and a.assign_Name=b.assign_Name and a.Assign_Date=b.maxAssignDate
三种方法,
1 in
2 exists
3 left join
--不管部门,只管人,安人分组
select * from tab a
where Assign_Date= (
select max(Assign_Date) from tab
where assign_Name=a. assign_Name
)
--1
select a.* from @t a,(select assign_Name, max(Assign_Date) Assign_Date from @t
group by assign_Name) b where a.assign_Name=b.assign_Name and a.Assign_Date=b.Assign_Date
--2
select * from tab a
where Assign_Date= (
select max(Assign_Date) from tab
where assign_Name=a. assign_Name
)
--3
select * from @t a where not exists (
select 1 from @t where assign_Name=a.assign_Name and Assign_Date>a.Assign_Date)