MySQL的distinct查询返回有重复信息的行，需要去重

Question

我在 MySQL 数据库中有一个类似于下面所示的表：

+----------+----------+----------+----------+----------+  
| Column_A | Column_B | Column_C | Column_D | Column_E |      
+----------+----------+----------+----------+----------+      
|        1 |       11 | a        |        0 | abc      |      
|        2 |       22 | a        |        0 | abc      |      
|        3 |       33 | a        |        0 | def      |      
|        4 |       44 | b        |        0 | def      |      
|        5 |          | b        |        0 | def      |      
|        6 |       55 | c        |        0 | ghi      |      
|        7 |          | d        |        0 | jkl      |      
|        8 |          | a        |        4 | abc      |      
|        9 |          | a        |        4 | abc      |      
|       10 |          | b        |        4 | abc      |      
|       11 |       88 | f        |        4 | xyz      |      
|       12 |          | f        |        4 | xyz      |      
+----------+----------+----------+----------+----------+      

我需要一个类似于下面的结果（即只有 a 和 b 值具有不同的列 D 和 E 值）：

+----------+----------+----------+
| Column_C | Column_D | Column_E |
+----------+----------+----------+
| a        |        0 | abc      |
| a        |        0 | def      |
| a        |        4 | abc      |
| b        |        0 | def      |
| b        |        4 | abc      |
+----------+----------+----------+

我尝试过这个查询：

SELECT DISTINCT column_c,column_d,column_e FROM trial2 ORDER BY column_c;

我得到这个：

+------------------+------------------+------------------+
|     column_c     |     column_d     |     column_e     |
+------------------+------------------+------------------+
|     a            |            0     |     abc          |
|     a            |            0     |     def          |
|     a            |            4     |     abc          |
|     b            |            0     |     def          |
|     b            |            4     |     abc          |
|     c            |            0     |     ghi          |
|     d            |            0     |     jkl          |
|     f            |            4     |     xyz          |
+------------------+------------------+------------------+

我不需要column_c中带有“c”、“d”或“f”的行。我需要同时具有 0 和 0 的行column_d 中有 4 个值（即，column_c 是“a”或“b”）。

Answer 1

您不需要加入...

SELECT column_c,column_d,column_e FROM trial2 
GROUP by column_c, column_d, column_e 
HAVING count (*) > 1 
ORDER BY column_c

having 子句在应用聚合后运行，因此您可以过滤分组后剩余的行数...

Answer 2

不太确定这个解决方案，但我认为它符合你的要求。

mysql> select * from randdata;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| a    | 0    | f    |
| a    | 2    | x    |
| b    | 2    | x    |
| c    | 0    | f    |
+------+------+------+
4 rows in set (0.00 sec)

mysql> select * from randdata GROUP BY concat(b,c);
+------+------+------+
| a    | b    | c    |
+------+------+------+
| a    | 0    | f    |
| a    | 2    | x    |
+------+------+------+
2 rows in set (0.01 sec)

询问：

select * from trial2 GROUP BY concat(column_d,column_e);

Answer 3

DISTINCT 仅确保一行在输出中最多出现一次。它不会删除与其他行不完全匹配的行。

要同时操作多行，您需要一个内部联接：

SELECT t.C, t.D, t.E
  FROM trial2 AS t
    JOIN trial2 AS tb
      ON t.C=tb.C AND (t.D != tb.D OR t.E != t.E)
  GROUP BY t.C, t.D, t.E
  ORDER BY t.C;

内部联接会过滤掉没有匹配行的行。在上面的查询中，匹配行是那些 C 列具有相同值但 D 或 E 列不同的行。