MySQL - 仅在 NOT NULL 时删除重复项，按两个字段使用 GROUP

Question

我想使用 GROUP BY 子句从我的表中删除重复项，但我只想在两个字段不为空时才这样做。如果任何字段为空，则分组依据不应删除此记录。

例子:

| ID | IDEK |
|  1 | null |
|null|  1   |
|null|  1   |
|null| null |
|665 | 22   |
|665 | 22   |

现在，当我使用这个查询时:

select * from test group by id, idek

我想得到这个结果:

| ID | IDEK |
|  1 | null | // HERE IS NULL SO IT SHOULD BE PRINTED ALWAYS
|null|  1   | // HERE IS NULL SO IT SHOULD BE PRINTED ALWAYS
|null|  1   | // HERE IS NULL SO IT SHOULD BE PRINTED ALWAYS
|null| null | // HERE IS NULL SO IT SHOULD BE PRINTED ALWAYS
|665 | 22   |

可以吗？这是它现在如何工作的现场示例:http://www.sqlfiddle.com/#!9/070218/1

谢谢。

Answer 1

这是您问题的解决方案:

SELECT id,idek 
FROM test
WHERE id IS NULL OR idek IS NULL
UNION ALL
SELECT id,idek 
FROM test
WHERE id IS NOT NULL AND idek IS NOT NULL
GROUP BY id,idek 

演示链接:

http://www.sqlfiddle.com/#!9/070218/8