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

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

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

Результат, что неудивительно,

<code>a   b
-------
A   NULL
A   B
B   NULL
</code>

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

<code>a   b
-------
A   B
B   NULL
</code>

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

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

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

 Jim Burnell23 апр. 2012 г., 16:23
Извините, я только что вернулся к этому сегодня. Для всех записей в группе каждый столбец либо: 100% заполнен одним значением, 100% NULL, либо частично заполнен одним значением и частично NULL. Например: (1, 2, NULL), (1, 2, NULL), (1, NULL, NULL) следует уменьшить до (1, 2, NULL).
 Mosty Mostacho21 апр. 2012 г., 01:01
Может значение столбцаa иметь более одного ненулевого значения вb? Если это так, ожидаете ли вы получить оба значения?

Ответы на вопрос(7)

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

create table test(
x char(1),
y char(1)
);

insert into test(x,y) values
('a',null),
('a','b'),
('b', null),
('b', null)

Запрос:

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

Живой тест:http://sqlfiddle.com/#!3/259d6/16

EDIT

ПроводыОтвет МостыЯ упростил свой код:

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

Я просто предпочитаю подход 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)
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

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
               ) 
             )
Решение Вопроса

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
 Jim Burnell23 апр. 2012 г., 16:56
Второй делает то, что мне нужно в этом случае. Каждый столбец полностью равен NULL, полностью заполнен одним значением или частично заполнен одним значением и частично равен NULL. MAX () решает проблему элегантно. Благодарю.
;WITH CTE
    AS
    (
    SELECT DISTINCT * FROM #test
    )
    SELECT a,b
    FROM CTE        
    ORDER BY CASE WHEN b IS NULL THEN 9999 ELSE b END ; 
 09 мар. 2017 г., 14:10
Не могли бы вы добавить краткое описание того, как работает ваше решение?

Ваш ответ на вопрос