SQL 消除 SELECT DISTINCT 中 NULL 的最简单方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10254435/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Easiest way to eliminate NULLs in SELECT DISTINCT?
提问by Jim Burnell
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
The result is, unsurprisingly,
结果不出所料，
a b
-------
A NULL
A B
B NULL
The output I would like to see in actuality is:
我希望实际看到的输出是：
a b
-------
A B
B 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.
也就是说，如果某列在某些记录中具有值但在其他记录中没有，我想将该列的值为 NULL 的行丢弃。但是，如果一列的所有记录都具有 NULL 值，我想保留该 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.
我有一种感觉，如果我在星期五下午没有筋疲力尽的话，这会很简单。
采纳答案by Mosty Mostacho
Try this:
尝试这个：
select distinct * from test
where b is not null or a in (
select a from test
group by a
having max(b) is null)
You can get the fiddle here.
你可以在这里得到小提琴。
Note if you can only have one non-null value in b, this can be simplified to:
请注意，如果 中只能有一个非空值b，则可以简化为：
select a, max(b) from test
group by a
回答by Karthika D
;WITH CTE
AS
(
SELECT DISTINCT * FROM #test
)
SELECT a,b
FROM CTE
ORDER BY CASE WHEN b IS NULL THEN 9999 ELSE b END ;
回答by Michael Buen
Try this:
尝试这个：
create table test(
x char(1),
y char(1)
);
insert into test(x,y) values
('a',null),
('a','b'),
('b', null),
('b', null)
Query:
询问：
with has_all_y_null as
(
select x
from test
group by x
having sum(case when y is null then 1 end) = count(x)
)
select distinct x,y from test
where
(
-- if a column has a value in some records but not in others,
x not in (select x from has_all_y_null)
-- I want to throw out the row with NULL
and y is not null
)
or
-- However, if a column has a NULL value for all records,
-- I want to preserve that NULL
(x in (select x from has_all_y_null))
order by x,y
Output:
输出：
X Y
A B
B NULL
Live test: http://sqlfiddle.com/#!3/259d6/16
现场测试：http: //sqlfiddle.com/#!3/259d6/16
EDIT
编辑
Seeing Mosty's answer, I simplified my code:
看到莫斯蒂的回答，我简化了我的代码：
with has_all_y_null as
(
select x
from test
group by x
-- having sum(case when y is null then 1 end) = count(x)
-- should have thought of this instead of the code above. Mosty's logic is good:
having max(y) is null
)
select distinct x,y from test
where
y is not null
or
(x in (select x from has_all_y_null))
order by x,y
I just prefer CTE approach, it has a more self-documenting logic :-)
我只是更喜欢 CTE 方法，它具有更多的自我记录逻辑:-)
You can also put documentation on non-CTE approach, if you are conscious of doing so:
如果您意识到这样做，您还可以将文档放在非 CTE 方法上：
select distinct * from test
where b is not null or a in
( -- has all b null
select a from test
group by a
having max(b) is null)
回答by John Dewey
SELECT a,b FROM #test t where b is not null
union
SELECT a,b FROM #test t where b is null
and not exists(select 1 from #test where a=t.a and b is not null)
Result:
结果：
a b
---- ----
A B
B NULL
回答by Akaize
I'll just put here a mix of two answers that solved my issue, because my View was more complex
我将在这里混合解决我的问题的两个答案，因为我的视图更复杂
--IdCompe int,
--Nome varchar(30),
--IdVanBanco int,
--IdVan int
--FlagAtivo bit,
--FlagPrincipal bit
select IdCompe
, Nome
, max(IdVanBanco)
, max(IdVan)
, CAST(MAX(CAST(FlagAtivo as INT)) AS BIT) FlagAtivo
, CAST(MAX(CAST(FlagPrincipal as INT)) AS BIT) FlagPrincipal
from VwVanBanco
where IdVan = {IdVan} or IdVan is null
group by IdCompe, Nome order by IdCompe asc
Thanks to mosty mostachoand kenwarner
回答by Chris Shaffer
Well, I don't particularly like this solution, but it seems the most appropriate to me. Note that your description of what you want sounds exactly like what you get with a LEFT JOIN, so:
嗯，我不是特别喜欢这个解决方案，但它似乎对我来说最合适。请注意，您对所需内容的描述听起来与使用 LEFT JOIN 得到的完全相同，因此：
SELECT DISTINCT a.a, b.b
FROM #test a
LEFT JOIN #test b ON a.a = b.a
AND b.b IS NOT NULL
回答by Andrew
SELECT DISTINCT t.a, t.b
FROM #test t
WHERE b IS NOT NULL
OR NOT EXISTS (SELECT 1 FROM #test u WHERE t.a = u.a AND u.b IS NOT NULL)
ORDER BY t.a, t.b
回答by ypercube??
This is a really weird requirement. I wonder how you need it.
这真是一个奇怪的要求。我想知道你怎么需要它。
SELECT DISTINCT a, b
FROM test t
WHERE NOT ( b IS NULL
AND EXISTS
( SELECT *
FROM test ta
WHERE ta.a = t.a
AND ta.b IS NOT NULL
)
)
AND NOT ( a IS NULL
AND EXISTS
( SELECT *
FROM test tb
WHERE tb.b = t.b
AND tb.a IS NOT NULL
)
)
