多重复条件列和同一天内数据的重复判断!

----------------------------删除重复数据过程----------------------------------------

-1--原表 mycareer.webjobs
-2----加入identity(int,1,1)的唯一数据操作表  emp
-3-----存放非重复数据id的id表
-4-----通过非重复数据表的id查询操作表非重复数据量
-5-----删除原表内容
-6------把非重复数据放进去

drop table emp ---------删除emp表
truncate table mp------彻底清除mp表数据

-------------------------将原表数据+唯一ID另存入emp表------------
select identity(int,1,1) as sid,datediff(day,w_time,getDate()) as nsm, * into emp from mycareer.webjobs
create table mp(mpid int not null)--------新建二表
    --------------CONVERT(datetime,(cast(w_time as varchar(10))))
----以组合条件查询出非重复数据的ID并插入指定表------------------
insert into mp(mpid) select min(sid) from emp group by jobname,w_name,w_company,nsm having count(jobname)>0

select *from emp order by w_time desc

select *from mp
-------通过存放非重复数据的ID来查询操作表的数据------------------------
select jobid,jobname,w_name,w_company,w_degree,w_experience,w_pay,w_time,w_area,w_url from emp where sid in(select mpid from mp)
---------删除原表内容---------
delete from mycareer.webjobs

-----将非重复数据再插回原表--------------
insert into mycareer.webjobs
 select jobid,jobname,w_name,w_company,w_degree,w_experience,w_pay,w_time,w_area,w_url from emp
 where sid in(select mpid from mp)

---测试------
select *from mycareer.webjobs order by w_time desc

drop table emp ---------删除emp表
drop table mp------彻底清除mp表数据

 

--------------------------------------------over .lrwin------------------------------------------