有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)会提示语法错误,请各位大侠指点一下.该如何实现这个功能?还有就是我表中的数据是百万条的记录,如何删除才能提高执行效率,缩短时间.谢谢各位了!
需要把表中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)会提示语法错误,请各位大侠指点一下.该如何实现这个功能?还有就是我表中的数据是百万条的记录,如何删除才能提高执行效率,缩短时间.谢谢各位了!
解决方案 »
- 请教sql (省份代码,城市代码,销量) 想选出每个省份中销量排名前20%的城市代码 内有测试数据 谢谢
- 我继续问那个存储过程的问题
- 难题,呵呵
- 大神们救我,小弟求教!
- 请问大家,对用select语句找出的那些表的数据在DataSet中能知道它们的字段名和表名么?
- Sql Server 锁表解决方案
- sql一道!
- 为什么执行delete from 表tb,只能删掉一行?
- (面试求解)请简单构造一个“SQL注入”漏洞发生得场景,即编写一个有问题的SQL访问代码,并模拟可能存在的安全问题。
- 大家帮助很急...............
- 关于日期类型和时间戳设计
- 恳请各位帮忙一下,我菜鸟搞不定啊,在线等
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 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)
(TITLE,BOOK_SEARC) in (select TITLE,BOOK_SEARC from ceshi group by TITLE,BOOK_SEARC having count(*)>1)如果是在sql server中,肯定是要报错的,建议你用下面的sql语句,就可以删除重复数据了,试试:
with t
as
(
select *,
row_number() over(paritition by TITLE,BOOK_SEARC
order by TITLE) as rown
from ceshi
)
delete from t
where rown = 2
with t
as
(
select *,
row_number() over(paritition by TITLE,BOOK_SEARC
order by TITLE) as rown
from ceshi
)
delete from t
where rown > 2
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 )
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用上面这个语句把结果存放到 temp_ceshi表。这个表里就是你需要的结果了。然后把表名改成ceshi。重复数据就去掉了