如何在SQL中删除重复的（两行混合）行？

作者: 一只特立独行的猪
发布时间: 2024-09-05 03:02:26 (25天前)
转自：

4 条回复

0#
回复此人
SHOU宅大可爱 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 的<strong> 只是 </强> ，删除重复的行并保持最低的ID </p> <pre> <code> DELETE t1 FROM jobs j1 NNER JOIN jobs j2 WHERE j1.id > j2.id AND j1.c1 = j2.c1 AND j1.c2 = j2.c2; </code> </pre> <P> 然后加 <code> UNIQUE index </code> 限制重复的条目 </p> <pre> <code> CREATE UNIQUE INDEX INDX_restrict_duplicate ON jobs(c1,c2); </code> </pre> </DIV>

编辑
1#
回复此人
求赞有赞必回 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 我假设表名是foobar，而c1和c2不可为空。 </p> <P> 此查询将选择副本 </p> <pre> <code> select d.* from ( select c1 ,c2 , count(*) as cnt , min(id) as mid from foobar group by c1,c1 ) as e join foobar d on d.c1=e.c1 and d.c2=e.c2 and d.id > e.mid ; </code> </pre> <P> 您必须创建一个临时表，其中包含要删除的所有ID的列表。 </p> <pre> <code> create table bad_id_foobar as select d.id from ( select c1 ,c2 , count(*) as cnt , min(id) as mid from foobar group by c1,c1 ) as e join foobar d on d.c1=e.c1 and d.c2=e.c2 and d.id > e.mid ; </code> </pre> <P> 此查询将删除副本 </p> <pre> <code> delete from foobar where id in ( select b.id from bad_id_foobar b ); </code> </pre> </DIV>

编辑
2#
回复此人
疯子哥哥 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 这可以通过三个步骤实现。 </p> <OL> <LI> 将唯一记录插入到tmp表中（与“作业”相同的表结构）。 </LI> </醇> <P> </p> <div class =“snippet”data-lang =“js”data-hide =“false”data-console =“true”data-babel =“false”> <div class =“snippet-code”> <pre class="snippet-code-html lang-html prettyprint-override"> <code> INSERT INTO tmp (`c1`, `c2`) SELECT c1, c2 FROM jobs GROUP BY c1, c2 </code> </pre> </DIV> </DIV> <P> </p> <ol start =“2”> <LI> 删除或重命名“作业”表 </LI> </醇> <P> </p> <div class =“snippet”data-lang =“js”data-hide =“false”data-console =“true”data-babel =“false”> <div class =“snippet-code”> <pre class="snippet-code-html lang-html prettyprint-override"> <code> DROP TABLE job </code> </pre> </DIV> </DIV> <P> </p> <P> </p> <div class =“snippet”data-lang =“js”data-hide =“false”data-console =“true”data-babel =“false”> <div class =“snippet-code”> <pre class="snippet-code-html lang-html prettyprint-override"> <code> ALTER TABLE job RENAME job_archived </code> </pre> </DIV> </DIV> <P> </p> <ol start =“3”> <LI> 将'tmp'表重命名为'job' </LI> </醇> <pre class="lang-html prettyprint-override"> <code> ALTER TABLE tmp RENAME job </code> </pre> <P> 这就是我如何做这样的任务。可能有更好的方法来做同样的事情......干杯！ </p> </DIV>

编辑

登录后才能参与评论