mysql - 使用合并在数据库中查找重复项

Question

我正在尝试检索 ACL 表中的重复项(我故意创建了一些)。

我编写了一个查询来检索它们，但不幸的是我的查询“无法完成这项工作”。我没有得到重复项，而是得到了表 ACL 的完整 View (就像 select * 所做的那样)，但项目已排序。重复项在里面，但它们并不像我想要的那样“隔离”。

通过这个查询，我只想检测重复项。重复项对我来说:每列中的信息完全相同(索引除外)，例如:

1  TOTO   BLABLA   123
2  TOTO   BLABLA   123

但这对我来说不是重复的:

1   TOTO    BLABLA  123
2   TOTO    WHAOUH  123

我的MYSQL版本是5.0.2。

我已经用谷歌搜索了它，但不幸的是我无法让它按照我想要的方式工作。

SELECT * FROM `acl` t1, 
                        (SELECT `vara`, `varb`, `varc`, `vard`, `vare`, `varf`, `varg`, `varh`, `vari`, `varj`, `vark`, `varl`, `varm`, `varn`, `varo`, `varp`, `varq`, `varr`, `vars`, `vart`, `varu`, `varv`
                          FROM `acl`
                          GROUP BY `vara`, `varb`, `varc`, `vard`, `vare`, `varf`, `varg`, `varh`, `vari`, `varj`, `vark`, `varl`, `varm`, `varn`, `varo`, `varp`, `varq`, `varr`, `vars`, `vart`, `varu`, `varv`
                        ) t2
                        WHERE 
                        coalesce(t1.`vara`, '1') = coalesce(t2.`vara`, '1')
                        AND coalesce(t1.`varb`, '1') = coalesce(t2.`varb`, '1')
                        AND coalesce(t1.`varc`, '1') = coalesce(t2.`varc`, '1')
                        AND coalesce(t1.`vard`, '1') = coalesce(t2.`vard`, '1')
                        AND coalesce(t1.`vare`, '1') = coalesce(t2.`vare`, '1')
                        AND coalesce(t1.`varf`, '1') = coalesce(t2.`varf`, '1')
                        AND coalesce(t1.`varg`, '1') = coalesce(t2.`varg`, '1')
                        AND coalesce(t1.`varh`, '1') = coalesce(t2.`varh`, '1')
                        AND coalesce(t1.`vari`, '1') = coalesce(t2.`vari`, '1')
                        AND coalesce(t1.`varj`, '1') = coalesce(t2.`varj`, '1')
                        AND coalesce(t1.`vark`, '1') = coalesce(t2.`vark`, '1')
                        AND coalesce(t1.`varl`, '1') = coalesce(t2.`varl`, '1')
                        AND coalesce(t1.`varm`, '1') = coalesce(t2.`varm`, '1')
                        AND coalesce(t1.`varn`, '1') = coalesce(t2.`varn`, '1')
                        AND coalesce(t1.`varo`, '1') = coalesce(t2.`varo`, '1')
                        AND coalesce(t1.`varp`, '1') = coalesce(t2.`varp`, '1')
                        AND coalesce(t1.`varq`, '1') = coalesce(t2.`varq`, '1')
                        AND coalesce(t1.`varr`, '1') = coalesce(t2.`varr`, '1')
                        AND coalesce(t1.`vars`, '1') = coalesce(t2.`vars`, '1')
                        AND coalesce(t1.`vart`, '1') = coalesce(t2.`vart`, '1')
                        AND coalesce(t1.`varu`, '1') = coalesce(t2.`varu`, '1')
                        AND coalesce(t1.`varv`, '1') = coalesce(t2.`varv`, '1') 
                        ";  

非常感谢您的回复!

Answer 1

为什么不直接使用group by？

SELECT `vara`, `varb`, `varc`, `vard`, `vare`, `varf`, `varg`, `varh`, `vari`, `varj`, `vark`, `varl`, `varm`, `varn`, `varo`, `varp`, `varq`, `varr`, `vars`, `vart`, `varu`, `varv`
FROM `acl`
GROUP BY `vara`, `varb`, `varc`, `vard`, `vare`, `varf`, `varg`, `varh`, `vari`, `varj`, `vark`, `varl`, `varm`, `varn`, `varo`, `varp`, `varq`, `varr`, `vars`, `vart`, `varu`, `varv`
HAVING COUNT(*) > 1;

如果您想查看 id 值，那么最简单的方法是添加 group_concat():

SELECT group_concat(id) as ids,
       `vara`, `varb`, `varc`, `vard`, `vare`, `varf`, `varg`, `varh`, `vari`, `varj`, `vark`, `varl`, `varm`, `varn`, `varo`, `varp`, `varq`, `varr`, `vars`, `vart`, `varu`, `varv`
FROM `acl`
GROUP BY `vara`, `varb`, `varc`, `vard`, `vare`, `varf`, `varg`, `varh`, `vari`, `varj`, `vark`, `varl`, `varm`, `varn`, `varo`, `varp`, `varq`, `varr`, `vars`, `vart`, `varu`, `varv`
HAVING COUNT(*) > 1;