如何在一定条件下删除重复项 [英] How to remove duplicate with certain condition

问题描述

我有一个mysql表，我想在一定条件下删除重复项

I have a mysql table I want to remove the duplicate with a certain condition

如果名称cl_1，cl_2，cl_3，cl_4，cl_5，cl_6完全相同，那么我想删除，如果名称不同而其他列相同，则无需删除.

I want to remove if name, cl_1, cl_2, cl_3, cl_4, cl_5, cl_6, are exactly same other wise if one was different and other columns were same no need to remove.

例如，我要删除第1行和第4行而不是第7行

for example I want to remove row 1 and 4 not 7

id  name    cl_1    cl_2    cl_3    cl_4    cl_5    cl_6    
--------------------------------------------------------------  
1   name1   T1      T2      T3      T4      T5      T6    (<< Remove)
2   name2   L1      M2      L3      L4      T5      T6
3   name3   T1      T2      T3      T4      T5      T6
4   name1   T1      T2      T3      T4      T5      T6  (<< Remove)
5   name2   T1      T2      T3      T4      T5      T6
6   name3   T1      T2      T3      T4      T5      T6
7   name1   T1      T2      T3      T4      T5      K6  (Not this one)
8   name5   T1      T2      T3      T4      T5      N6
9   name6   T1      T2      T3      T4      T5      H6

推荐答案

这应该有效:

DELETE Y
FROM YourTable Y JOIN 
   (
  SELECT
    name, cl_1, cl_2, cl_3, cl_4, cl_5, cl_6 
  FROM YourTable
  GROUP BY name, cl_1, cl_2, cl_3, cl_4, cl_5, cl_6
  HAVING COUNT(1) > 1
     ) T ON Y.name = T.name 
        AND Y.cl_1 = T.cl_1
        AND Y.cl_2 = T.cl_2
        AND Y.cl_3 = T.cl_3
        AND Y.cl_4 = T.cl_4
        AND Y.cl_5 = T.cl_5
        AND Y.cl_6 = T.cl_6

SQL小提琴演示

顺便说一句-第3和第6行呢?那些看起来完全一样吗?

BTW -- What about rows 3 and 6? Those seem to be the exact same?

http://sqlfiddle.com/#!2/26b8b/1

这篇关于如何在一定条件下删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！