sql - 将空值分组为与非值字段匹配以检查 SQL 中的重复项

Question

我正在尝试改进/优化我的查询以检查表 TABLE1 中的重复项。主要要求是将空值视为通配符。

所以给出下表：

将空字段视为通配符，例如 'A' = null 为真。预期的结果是组中每条记录的记录详细信息以及组标识符，即表最右侧的 id 和 id1：

使用此查询已经实现：

WITH CTE
    AS (SELECT t1.id, t2.id as id1 FROM TABLE1 t1
             INNER JOIN TABLE1 t2 
                ON (t1.column1 = t2.column1 OR (t1.column1 IS NULL OR t2.column1 IS NULL))
                AND (t1.column2 = t2.column2 OR (t1.column2 IS NULL OR t2.column2 IS NULL))
                AND (t1.column3 = t2.column3 OR (t1.column3 IS NULL OR t2.column3 IS NULL))
                AND (t1.column4 = t2.column4 OR (t1.column4 IS NULL OR t2.column4 IS NULL))
                AND (t1.column5 = t2.column5 OR (t1.column5 IS NULL OR t2.column5 IS NULL))
                AND (t1.column6 = t2.column6 OR (t1.column6 IS NULL OR t2.column6 IS NULL))
        WHERE t2.id > t1.id)
    SELECT *
    FROM TABLE1 t1
        INNER JOIN CTE C
        ON t1.id = c.id
        OR t1.id = c.id1

我还在使用包含 Id、column1、column2、column3、column4、column5 和column6 的索引。该查询对于小表运行正常，但是当我使用数百万个数据运行它时，它会永远运行。

我尝试使用 joins 和 isnull 但这似乎没有什么不同。

标签： sqldatabaseperformanceoptimizationnull

Answer 1

您可能会考虑以下内容（在 MySQL 中，如果重要的话）...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,col1 CHAR(1) NULL
,col2 CHAR(1) NULL
,col3 CHAR(1) NULL
);

 INSERT INTO my_table VALUES
 (1,'A','B',NULL),
 (2,'A',NULL,'C');

 SELECT * FROM my_table;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | A    | B    | NULL |
|  2 | A    | NULL | C    |
+----+------+------+------+
2 rows in set (0.01 sec)

SELECT a.*
     , b.*
  FROM 
     ( SELECT id, 'c1' col, col1 val FROM my_table
        UNION ALL
       SELECT id, 'c2', col2 FROM my_table
        UNION ALL
       SELECT id, 'c3', col3 FROM my_table
     ) a
  JOIN
     ( SELECT id, 'c1' col, col1 val FROM my_table
        UNION ALL
       SELECT id, 'c2', col2 FROM my_table
        UNION ALL
       SELECT id, 'c3', col3 FROM my_table
     ) b
    ON b.id > a.id
   AND b.col = a.col
   AND (b.val = a.val OR b.val IS NULL or a.val IS NULL);

   +----+-----+------+----+-----+------+
   | id | col | val  | id | col | val  |
   +----+-----+------+----+-----+------+
   |  1 | c1  | A    |  2 | c1  | A    |
   |  1 | c2  | B    |  2 | c2  | NULL |
   |  1 | c3  | NULL |  2 | c3  | C    |
   +----+-----+------+----+-----+------+