如何找到以及清除一个数据表中,两个列中数值相同的数据行

比如说一个数据库有id,name,score三列,其中存在name与score均相同的两行或多行。如何清除name与score都相同的重复行呢

  • id name score
  • 1 homura 100
  • 2 madoka 100
  • 3 sayaka 80
  • 4 sayaka 80
  • 5 homura 99 比如这个例子,需要查找到name与score都相同的行(sayaka 80)并且清除 怎么写sql语句才能够达到找到这些的效果并清除掉重复项仅保留一个啊(查找的语句和清除的语句希望分别给出的说,分可以加- -)

5个回答

先找出相同行的id,sql如下:

SELECT
    DISTINCT(t_s.id)
FROM
    `t_score` t_s
    INNER JOIN ( SELECT * FROM t_score ) AS ts ON t_s.NAME = ts.NAME 
    AND t_s.score = ts.score 
    AND t_s.id != ts.id
    order by t_s.id asc;

如果删除的话,在这个基础上,再加一层删除操作:

DELETE 
FROM
    t_score 
WHERE
    id IN (
    SELECT
        a.id 
    FROM
        (
        SELECT DISTINCT
            ( t_s.id ) AS id 
        FROM
            `t_score` t_s
            INNER JOIN ( SELECT * FROM t_score ) AS ts ON t_s.NAME = ts.NAME 
            AND t_s.score = ts.score 
            AND t_s.id != ts.id 
        ORDER BY
            t_s.id ASC 
        ) a 
    );

亲测有效,希望可以帮助你!(不要忘了给分哦!)

select DISTINCT from table

select * from tab t
where (t.name,t.score) in (select name,score from tab group by name,score having count(*)> 1 -- 查询重复的数据
and rowid not in (select min(rowid) from tab group by name,score having count(*)>1); -- 加上这个可以保留一个

rowid 是oracle的唯一标识 mysql用_rowid试下

删除就是把查到的删掉就行

查询:
select * from test where id not in( select MAX(id) as id from test group by name, score) //test改为你自己的数据库名
删除:
delete from test where id in (
select test1.id from test as test1, test as test2 where test1.name = test2.name and test1.score = test2.score and test1.id < test2.id)
应该还是挺容易理解的

试试看
--select
select * from your_table where id in (select max(id) from your_table group by name, score having count(id)>1)
--delete
delete from your_table where id in (select max(id) from your_table group by name, score having count(id)>1);

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问