急急急 ,如何删除重复数据
有ceshi表 :
需要把表中TITLE,BOOK_SEARC这两个字段重复的记录删除,只保留一条数据 .
写了这个SQL语句 :
delete ceshi where (TITLE,BOOK_SEARC) in (select TITLE,BOOK_SEARC from ceshi group by TITLE,BOOK_SEARC having count(*)>1)
and BOOK_BARCO not in(select min(BOOK_BARCO) from ceshi group by TITLE,BOOK_SEARC having count(*)>1)
会提示语法错误,请各位大侠指点一下.该如何实现这个功能?还有就是我表中的数据是百万条的记录,如何删除才能提高执行效率,缩短时间.谢谢各位了!
[解决办法]
delete ceshi
where BOOK_BARCO in
(select BOOK_BARCO
from (select *,rn=row_number() over(partition by TITLE,BOOK_SEARC order by BOOK_BARCO) from tb)t
where rn>1
)
DELETE ceshi
WHERE EXISTS ( SELECT 1
FROM ( SELECT TITLE ,
BOOK_SEARC
FROM ceshi
GROUP BY TITLE ,
BOOK_SEARC
HAVING COUNT(*) > 1
) b
WHERE ceshi.title = b.title
AND ceshi.book_searc = b.book_searc )
with t
as
(
select *,
row_number() over(paritition by TITLE,BOOK_SEARC
order by TITLE) as rown
from ceshi
)
delete from t
where rown > 1
select *
into temp_ceshi
from
(
select *,ROW_NUMBER() over(partition by TITLE,BOOK_SEARC ,order by getdate()) rn
from ceshi
) t
where rn=1
