根据一个字段查找不重复的记录（不包括空值）。 [英] Find non-duplicate records, excluding nulls, based on one field.
本文介绍了根据一个字段查找不重复的记录（不包括空值）。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想在Dat1字段中找到所有唯一记录，但是我想返回每一个Null记录。删除哪个重复记录都没关系。
I want to find all unique records in the Dat1 field, but I want every single Null record returned. It doesn't matter which duplicate record is dropped.
示例表：
+----+--------------+
| ID | Dat1 |
+----+--------------+
| 1 | 11@email.com |
| 2 | 11@email.com |
| 3 | NULL |
| 4 | NULL |
| 5 | 99@email.com |
| 6 | 99@email.com |
+----+--------------+
所需结果：
+----+--------------+
| ID | Dat1 |
+----+--------------+
| 1 | 11@email.com |
| 3 | NULL |
| 4 | NULL |
| 5 | 99@email.com |
+----+--------------+
这可能吗？我尝试了几种使用子查询的方法，但还不能完全实现。
Is this possible? I tried a couple of approaches with sub-queries but couldn't quite pull it off.
推荐答案
您可以使用 ROW_NUMBER 消除非空重复项 Dat1 s，然后使用 UNION ALL 添加 NULL 值：
You can use ROW_NUMBER to eliminate duplicate for non-null Dat1s and then use UNION ALL to add the NULL values:
WITH tbl(Id, Dat1) AS(
SELECT 1, '11@email.com' UNION ALL
SELECT 2, '11@email.com' UNION ALL
SELECT 3, NULL UNION ALL
SELECT 4, NULL UNION ALL
SELECT 5, '99@email.com' UNION ALL
SELECT 6, '99@email.com'
)
SELECT
Id, Dat1
FROM (
SELECT *,
rn = ROW_NUMBER() OVER(PARTITION BY Dat1 ORDER BY Id)
FROM tbl
WHERE Dat1 IS NOT NULL
)t
WHERE rn = 1
UNION ALL
SELECT * FROM tbl WHERE Dat1 IS NULL
这篇关于根据一个字段查找不重复的记录（不包括空值）。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
