根据某些条件删除表中的重复数据

1.　　首先先查到哪些符合条件的数据的数量大于1

SELECT
    max( id ) AS id,
    zy_id,
    student_id,
    question_id_char,
    count( 1 ) 
FROM
    t_base_xx_zy_student_answer 
GROUP BY
    zy_id,
    student_id,
    question_id_char 
HAVING
    count( 1 ) > 1

2.　　然后再根据条件找到这些重复数据的相应id（或者拿到别的标识）

SELECT
t1.id 
FROM
    t_base_xx_zy_student_answer t1,
    (
SELECT
    max( id ) AS id,
    zy_id,
    student_id,
    question_id_char,
    count( 1 ) 
FROM
    t_base_xx_zy_student_answer 
GROUP BY
    zy_id,
    student_id,
    question_id_char 
HAVING
    count( 1 ) > 1 
    ) t2 
WHERE
    t1.zy_id = t2.zy_id 
    AND t1.student_id = t2.student_id 
    AND t1.question_id_char = t2.question_id_char 
    AND t1.id != t2.id;

3.　　创建一个临时的表xx_del_id 

CREATE TABLE xx_del_id SELECT
t1.id 
FROM
    t_base_xx_zy_student_answer t1,
    (
SELECT
    max( id ) AS id,
    zy_id,
    student_id,
    question_id_char,
    count( 1 ) 
FROM
    t_base_xx_zy_student_answer 
GROUP BY
    zy_id,
    student_id,
    question_id_char 
HAVING
    count( 1 ) > 1 
    ) t2 
WHERE
    t1.zy_id = t2.zy_id 
    AND t1.student_id = t2.student_id 
    AND t1.question_id_char = t2.question_id_char 
    AND t1.id != t2.id;

4.　　然后在表中批量删掉符合条件的id

DELETE 
FROM
    t_base_xx_zy_student_answer 
WHERE
    id IN ( SELECT id FROM xx_del_id );

5.　　在上面创建临时表之后使用完成之后需要删除

DROP TABLE
IF
    EXISTS `xx_del_id`;

最后整体的sql语句

 1 DROP TABLE
 2 IF
 3     EXISTS `xx_del_id`;
 4 CREATE TABLE xx_del_id SELECT
 5 t1.id 
 6 FROM
 7     t_base_xx_zy_student_answer t1,
 8     (
 9 SELECT
10     max( id ) AS id,
11     zy_id,
12     student_id,
13     question_id_char,
14     count( 1 ) 
15 FROM
16     t_base_xx_zy_student_answer 
17 GROUP BY
18     zy_id,
19     student_id,
20     question_id_char 
21 HAVING
22     count( 1 ) > 1 
23     ) t2 
24 WHERE
25     t1.zy_id = t2.zy_id 
26     AND t1.student_id = t2.student_id 
27     AND t1.question_id_char = t2.question_id_char 
28     AND t1.id != t2.id;
29 DELETE 
30 FROM
31     t_base_xx_zy_student_answer 
32 WHERE
33     id IN ( SELECT id FROM xx_del_id );
34 DROP TABLE
35 IF
36     EXISTS `xx_del_id`;

上面的可能有问题，后期用到遇到问题再进行相应修改

posted @ 2019-02-22 16:48  程序猿雪儿  阅读(520)  评论(0)  编辑  收藏  举报