--聲明變量
Declare @ID Int, @PosNo Int
Declare @Table Table(
ID Int,
PosNo int
)
Declare cur Cursor Static READ_ONLY For
Select ID, PosNo
From 表
Order By EmpID, ID
Open cur
Fetch From cur Into @ID, @PosNo
While @@FETCH_STATUS = 0
Begin
If Exists(Select * From @Table Where PosNo = @PosNo)
Begin
Delete 表 Where ID = @ID
End
Else
Begin
Insert Into @Table (ID, PosNo)
Values (@ID, @PosNo)
End

Fetch Next From cur Into @ID, @PosNo
End
Close cur
DeAllocate cur

select *
from (
select a.*,row_number() over (parition by Empid,CardNO,MACHNO,POSNUM,type order by id desc) rn
from table_name a
) t
where rn<=1