Самый простой способ устранить NULL в SELECT DISTINCT?

Я работаю над запросом, который довольно похож на следующий:

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

Вывод, который я хотел бы увидеть на самом деле:

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

То есть, если столбец имеет значение в некоторых записях, но не в других, я хочу выкинуть строку с NULL для этого столбца. Однако, если столбец имеет значение NULL для всех записей, я хочу сохранить этот NULL.

Какой самый простой/самый элегантный способ сделать это в одном запросе?

У меня такое чувство, что это было бы просто, если бы я не был исчерпан в пятницу днем.

Ответ 1

Попробуйте следующее:

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, это можно упростить до:

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

Ответ 2

Попробуйте следующее:

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

Вывод:

 X    Y
 A    B
 B    NULL

Live test: http://sqlfiddle.com/#!3/259d6/16

ИЗМЕНИТЬ

Увидев Мости ответ, я упростил свой код:

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 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

Я просто предпочитаю подход CTE, у него есть более самостоятельная документальная логика: -)

Вы также можете разместить документацию по не-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)

Ответ 3

;WITH CTE
    AS
    (
    SELECT DISTINCT * FROM #test
    )
    SELECT a,b
    FROM CTE        
    ORDER BY CASE WHEN b IS NULL THEN 9999 ELSE b END ;

Ответ 4

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

Ответ 5

Это действительно странное требование. Интересно, как вам это нужно.

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
               ) 
             )

Ответ 6

Ну, мне это не особенно нравится, но мне кажется, что это самое подходящее для меня. Обратите внимание, что ваше описание того, что вы хотите, похоже на то, что вы получаете с помощью 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

Ответ 7

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)

Результат:

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

Ответ 8

Я просто добавлю сюда два ответа, которые решили мою проблему, потому что мой взгляд был более сложным

    --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

Благодаря Мосту Мостачо и kenwarner