Início / dba / Perguntas / 28647
Accepted
cww
Asked: 2012-11-14 22:20:38 +0800 CST

MySQL verifica duplicado com grupo usando curinga?

  • 772
    +----+--------------+-----+-----------+----------+
    | ID | NAME         | AGE | ADDRESS   | SALARY   |
    +----+--------------+-----+-----------+----------+
    |  1 | Ramesh Olive |  32 | Ahmedabad |  2000.00 |
    |  2 | Tan Kau      |  25 | Delhi     |  1500.00 |
    |  3 | Jason Tan Kau|  25 | Delhi     |  2000.00 |
    |  4 | Chaitali     |  25 | Mumbai    |  6500.00 |
    |  5 | Hardik       |  27 | Bhopal    |  8500.00 |
    |  6 | Hardik Jass  |  27 | Bhopal    |  4500.00 |
    |  7 | Muffy John   |  24 | Indore    | 10000.00 |
    |  8 | Muffy Lee    |  24 | Indore    | 10000.00 |
    +----+--------------+-----+-----------+----------+

No exemplo acima, vamos dizer que o nome da tabela é "table_a" e 1) "Tan Kau" é duplicado com "Jason Tan Kau" e 2) "Hardik" é duplicado com "Hardik Jass"

Como escrever SQL que produzirá uma saída como abaixo?

Eu acho que isso vai funcionar, mas deve ser muito lento. Alguma ideia para melhorar isso?

Select A.*, IF(B.ID IS NULL, "", "DUP") as DUP
FROM table_a A 
LEFT JOIN table_a B 
ON A.NAME LIKE CONCATE("%", B.NAME, "%") AND A.ID != B.ID


    +----+--------------+-----+-----------+----------+-----+
    | ID | NAME         | AGE | ADDRESS   | SALARY   | DUP |
    +----+--------------+-----+-----------+----------+-----+
    |  1 | Ramesh Olive |  32 | Ahmedabad |  2000.00 |     |
    |  2 | Tan Kau      |  25 | Delhi     |  1500.00 | Dup |
    |  3 | Jason Tan Kau|  25 | Delhi     |  2000.00 | Dup |
    |  4 | Chaitali     |  25 | Mumbai    |  6500.00 |     |
    |  5 | Hardik       |  27 | Bhopal    |  8500.00 | Dup |
    |  6 | Hardik Jass  |  27 | Bhopal    |  4500.00 | Dup | 
    |  7 | Muffy John   |  24 | Indore    | 10000.00 |     |
    |  8 | Muffy Lee    |  24 | Indore    | 10000.00 |     |
    +----+--------------+-----+-----------+----------+-----+
mysql duplication

2 respostas

  1. Best Answer

    Sua consulta pode retornar os resultados esperados adicionando a condição inversa:

    SELECT A.*, IF(B.ID IS NULL, "", "DUP") as DUP
FROM persons A 
LEFT JOIN persons B 
ON a.ID <> b.ID 
AND (a.Name LIKE CONCAT ("%", b.Name, "%") OR b.Name LIKE CONCAT ("%", a.Name, "%"))
ORDER BY ID;

    Não sei se será mais rápido, mas outra forma de fazer seria usar o INSTR:

    SELECT A.*, IF(B.ID IS NULL, "", "DUP") as DUP
FROM persons A 
LEFT JOIN persons B 
ON a.ID <> b.ID 
AND (Instr(a.Name, b.Name) > 0 OR Instr(b.Name, a.Name) > 0)
ORDER BY ID;

    SQL Fiddle

    • 3

  2. eu fiz algo um pouco diferente

    SELECT DISTINCT AA.* FROM
(
    SELECT A.*,IF(IFNULL(B.ID,'')='','','Dup') DUP
    FROM table_a A LEFT JOIN table_a B ON a.ID <> b.ID 
    AND IF(LENGTH(A.name)>LENGTH(B.name),
    INSTR(A.name,B.name)>0,
    INSTR(B.name,A.name)>0)
) AA;

    NOTA: Eu basicamente plagiei a resposta de Leigh e a aumentei um pouco, então, por favor, não marque minha resposta como aceita !!!

    A razão pela qual dei isso é para o caso de haver mais dups presentes

    Aqui estão seus dados de amostra mais duas linhas extras:

    mysql> DROP DATABASE IF EXISTS cww;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE cww;
Query OK, 1 row affected (0.00 sec)

mysql> USE cww
Database changed
mysql> CREATE TABLE table_a
    -> (
    ->     ID INT NOT NULL AUTO_INCREMENT,
    ->     NAME VARCHAR(25) NOT NULL,
    ->     AGE INT NOT NULL,
    ->     ADDRESS VARCHAR(25) NOT NULL,
    ->     SALARY DECIMAL(10,2) NOT NULL,
    ->     PRIMARY KEY (ID)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO table_a (NAME,AGE,ADDRESS,SALARY) VALUES
    -> ('Ramesh Olive'   ,32,'Ahmedabad', 2000.00),
    -> ('Tan Kau'        ,25,'Delhi'    , 1500.00),
    -> ('Jason Tan Kau'  ,25,'Delhi'    , 2000.00),
    -> ('Jackson Tan Kau',25,'Delhi'    , 2000.00),
    -> ('Chaitali'       ,25,'Mumbai'   , 6500.00),
    -> ('Hardik'         ,27,'Bhopal'   , 8500.00),
    -> ('Hardik Jass'    ,27,'Bhopal'   , 4500.00),
    -> ('Hardik Jess'    ,27,'Bhopal'   , 4500.00),
    -> ('Muffy John'     ,24,'Indore'   , 10000.00),
    -> ('Muffy Lee'      ,24,'Indore'   , 10000.00);
Query OK, 10 rows affected (0.05 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM table_a;
+----+-----------------+-----+-----------+----------+
| ID | NAME            | AGE | ADDRESS   | SALARY   |
+----+-----------------+-----+-----------+----------+
|  1 | Ramesh Olive    |  32 | Ahmedabad |  2000.00 |
|  2 | Tan Kau         |  25 | Delhi     |  1500.00 |
|  3 | Jason Tan Kau   |  25 | Delhi     |  2000.00 |
|  4 | Jackson Tan Kau |  25 | Delhi     |  2000.00 |
|  5 | Chaitali        |  25 | Mumbai    |  6500.00 |
|  6 | Hardik          |  27 | Bhopal    |  8500.00 |
|  7 | Hardik Jass     |  27 | Bhopal    |  4500.00 |
|  8 | Hardik Jess     |  27 | Bhopal    |  4500.00 |
|  9 | Muffy John      |  24 | Indore    | 10000.00 |
| 10 | Muffy Lee       |  24 | Indore    | 10000.00 |
+----+-----------------+-----+-----------+----------+
10 rows in set (0.00 sec)

mysql>

    Observe como minha consulta aumentada lida com os dups corretamente

    mysql> SELECT DISTINCT AA.* FROM
    -> (
    ->     SELECT A.*,IF(IFNULL(B.ID,'')='','','Dup') DUP
    ->     FROM table_a A LEFT JOIN table_a B ON a.ID <> b.ID
    ->     AND IF(LENGTH(A.name)>LENGTH(B.name),
    ->     INSTR(A.name,B.name)>0,
    ->     INSTR(B.name,A.name)>0)
    -> ) AA;
+----+-----------------+-----+-----------+----------+-----+
| ID | NAME            | AGE | ADDRESS   | SALARY   | DUP |
+----+-----------------+-----+-----------+----------+-----+
|  1 | Ramesh Olive    |  32 | Ahmedabad |  2000.00 |     |
|  2 | Tan Kau         |  25 | Delhi     |  1500.00 | Dup |
|  3 | Jason Tan Kau   |  25 | Delhi     |  2000.00 | Dup |
|  4 | Jackson Tan Kau |  25 | Delhi     |  2000.00 | Dup |
|  5 | Chaitali        |  25 | Mumbai    |  6500.00 |     |
|  6 | Hardik          |  27 | Bhopal    |  8500.00 | Dup |
|  7 | Hardik Jass     |  27 | Bhopal    |  4500.00 | Dup |
|  8 | Hardik Jess     |  27 | Bhopal    |  4500.00 | Dup |
|  9 | Muffy John      |  24 | Indore    | 10000.00 |     |
| 10 | Muffy Lee       |  24 | Indore    | 10000.00 |     |
+----+-----------------+-----+-----------+----------+-----+
10 rows in set (0.00 sec)

mysql>

    Diante de mais dups, a consulta de Leigh faz o seguinte:

    mysql> SELECT A.*, IF(B.ID IS NULL, "", "DUP") as DUP
    -> FROM table_a A
    -> LEFT JOIN table_a B
    -> ON a.ID <> b.ID
    -> AND (Instr(a.Name, b.Name) > 0 OR Instr(b.Name, a.Name) > 0)
    -> ORDER BY ID;
+----+-----------------+-----+-----------+----------+-----+
| ID | NAME            | AGE | ADDRESS   | SALARY   | DUP |
+----+-----------------+-----+-----------+----------+-----+
|  1 | Ramesh Olive    |  32 | Ahmedabad |  2000.00 |     |
|  2 | Tan Kau         |  25 | Delhi     |  1500.00 | DUP |
|  2 | Tan Kau         |  25 | Delhi     |  1500.00 | DUP |
|  3 | Jason Tan Kau   |  25 | Delhi     |  2000.00 | DUP |
|  4 | Jackson Tan Kau |  25 | Delhi     |  2000.00 | DUP |
|  5 | Chaitali        |  25 | Mumbai    |  6500.00 |     |
|  6 | Hardik          |  27 | Bhopal    |  8500.00 | DUP |
|  6 | Hardik          |  27 | Bhopal    |  8500.00 | DUP |
|  7 | Hardik Jass     |  27 | Bhopal    |  4500.00 | DUP |
|  8 | Hardik Jess     |  27 | Bhopal    |  4500.00 | DUP |
|  9 | Muffy John      |  24 | Indore    | 10000.00 |     |
| 10 | Muffy Lee       |  24 | Indore    | 10000.00 |     |
+----+-----------------+-----+-----------+----------+-----+
12 rows in set (0.00 sec)

mysql>

    A resposta de @LeighRiffel só precisa ser incorporada em uma subconsulta e tornada DISTINTA:

    mysql> SELECT DISTINCT * FROM (
    -> SELECT A.*, IF(B.ID IS NULL, "", "DUP") as DUP
    -> FROM table_a A
    -> LEFT JOIN table_a B
    -> ON a.ID <> b.ID
    -> AND (Instr(a.Name, b.Name) > 0 OR Instr(b.Name, a.Name) > 0)
    -> ORDER BY ID) AA;
+----+-----------------+-----+-----------+----------+-----+
| ID | NAME            | AGE | ADDRESS   | SALARY   | DUP |
+----+-----------------+-----+-----------+----------+-----+
|  1 | Ramesh Olive    |  32 | Ahmedabad |  2000.00 |     |
|  2 | Tan Kau         |  25 | Delhi     |  1500.00 | DUP |
|  3 | Jason Tan Kau   |  25 | Delhi     |  2000.00 | DUP |
|  4 | Jackson Tan Kau |  25 | Delhi     |  2000.00 | DUP |
|  5 | Chaitali        |  25 | Mumbai    |  6500.00 |     |
|  6 | Hardik          |  27 | Bhopal    |  8500.00 | DUP |
|  7 | Hardik Jass     |  27 | Bhopal    |  4500.00 | DUP |
|  8 | Hardik Jess     |  27 | Bhopal    |  4500.00 | DUP |
|  9 | Muffy John      |  24 | Indore    | 10000.00 |     |
| 10 | Muffy Lee       |  24 | Indore    | 10000.00 |     |
+----+-----------------+-----+-----------+----------+-----+
10 rows in set (0.00 sec)

mysql>

    No entanto, a resposta de Leigh forneceu os princípios SQL necessários antecipadamente.

    Portanto, dou a ele um +1 !!!

    • 1

relate perguntas