消除 SELECT DISTINCT 中 NULL 的最简单方法? [英] Easiest way to eliminate NULLs in SELECT DISTINCT?

问题描述

我正在处理一个与以下内容非常相似的查询:

I am working on a query that is fairly similar the following:

CREATE TABLE #test (a char(1), b char(1))

INSERT INTO #test(a,b) VALUES 
('A',NULL),
('A','B'),
('B',NULL),
('B',NULL)

SELECT DISTINCT a,b FROM #test

DROP TABLE #test

不出所料，结果是

a   b
-------
A   NULL
A   B
B   NULL

我希望实际看到的输出是:

The output I would like to see in actuality is:

a   b
-------
A   B
B   NULL

也就是说，如果某列在某些记录中有值但在其他记录中没有，我想将该列的值为 NULL 的行丢弃.但是，如果一列的所有记录都具有 NULL 值，我想保留该 NULL.

That is, if a column has a value in some records but not in others, I want to throw out the row with NULL for that column. However, if a column has a NULL value for all records, I want to preserve that NULL.

在单个查询中执行此操作的最简单/最优雅的方法是什么?

What's the simplest/most elegant way to do this in a single query?

我有一种感觉，如果我不是在周五下午筋疲力尽的话，这会很简单.

I have a feeling that this would be simple if I weren't exhausted on a Friday afternoon.

推荐答案

试试这个:

select distinct * from test
where b is not null or a in (
  select a from test
  group by a
  having max(b) is null)

您可以在此处获取小提琴.

注意如果b中只能有一个非空值，可以简化为:

Note if you can only have one non-null value in b, this can be simplified to:

select a, max(b) from test
group by a

这篇关于消除 SELECT DISTINCT 中 NULL 的最简单方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！