use book
go
create table ##T1(
n int,
a nvarchar(20)
)
--查詢重複記錄，插入臨時表
insert into ##T1(n,a)
select suid,WorkID from HR_Duty_EmpSchedule_M
where WorkID in (select WorkID from HR_Duty_EmpSchedule_M group by WorkID having count(WorkID) > 1)
and DutyDate ='2018/11/26'
order by WorkID
select * from ##T1
create table ##T2(
m int,
a nvarchar(20)
)
--查詢
select min(n) as N from ##T1 group by a having count(a)>1
insert into ##T2(m,a)
select n,a from ##T1 where n not in ( select min(n) as N from ##T1 group by a having count(a)>1)
select * from ##T2
select * from HR_Duty_EmpSchedule_M X,##T2 Y where X.SUID=Y.m order by WorkID
--delete from HR_Duty_EmpSchedule_M where SUID in (select m from ##T2)
drop table ##T1
drop table ##T2