Próbuję wymyślić proste zapytanie, które mogę wykonać, aby przetestować, czy duża tabela ma listę wpisów, która ma co najmniej JEDNĄ wartość pustą (NULL / pustą) w DOWOLNEJ kolumnie.
Potrzebuję czegoś takiego
SELECT * FROM table AS t WHERE ANY(t.* IS NULL)Nie chcę tego robić
SELECT * FROM table AS t WHERE t.c1 = NULL OR t.c2 = NULL OR t.c3 = NULLTo byłoby OGROMNE zapytanie.
Odpowiedzi:
Rozszerzenie odpowiedzi @ db2 z mniejszym (czytaj: zero) ręcznym kłótniami:
DECLARE @tb nvarchar(512) = N'dbo.[table]';
DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
+ ' WHERE 1 = 0';
SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tb);
EXEC sys.sp_executesql @sql;Powinieneś wymienić wszystkie kolumny zgodnie z komentarzem JNK.
WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULLPoniżej znajduje się nieco mniej wydajne podejście, które pozwala tego uniknąć.
;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS ns)
SELECT *
FROM YourTable AS T1
WHERE (
SELECT T1.*
FOR XML PATH('row'), ELEMENTS XSINIL, TYPE
).exist('//*/@ns:nil') = 1 Nie ma ładnej wbudowanej składni, ale Management Studio ma kilka wygodnych funkcji umożliwiających szybkie generowanie zapytania.
W Eksploratorze obiektów przejdź do odpowiedniej tabeli, rozwiń ją, a następnie przeciągnij cały folder „Kolumny” do pustego edytora zapytań. Spowoduje to dodanie do zapytania listy kolumn oddzielonych przecinkami.
Następnie otwórz Znajdź i zamień. Ustaw „Znajdź co” ,i ustaw „Zamień na” na IS NULL OR(z wiodącym odstępem), a następnie naciśnij Zamień wszystko. Musisz ręcznie wyczyścić ostatni w sekwencji.
Wciąż jest brzydka, ale mniej brzydka pracochłonna.
Jeśli chcesz przetestować wiele kolumn, możesz użyć następujących opcji:
Column_1 Column_2 Column_3
-------- -------- --------
1 2 NULL
1 NULL NULL
5 6 NULLNajpierw sprawdź NULL i policz je:
select
sum(case when Column_1 is null then 1 else 0 end) as Column_1,
sum(case when Column_2 is null then 1 else 0 end) as Column_2,
sum(case when Column_3 is null then 1 else 0 end) as Column_3,
from TestTable Daje liczbę NULL:
Column_1 Column_2 Column_3
0 1 3Jeśli wynik wynosi 0, nie ma wartości NULL.
Po drugie , policzmy wartości inne niż NULL:
select
sum(case when Column_1 is null then 0 else 1 end) as Column_1,
sum(case when Column_2 is null then 0 else 1 end) as Column_2,
sum(case when Column_3 is null then 0 else 1 end) as Column_3,
from TestTable... Ale ponieważ liczymy tutaj wartości inne niż NULL, można to uprościć:
select
count(Column_1) as Column_1,
count(Column_2) as Column_2,
count(Column_3) as Column_3,
from TestTableKażdy z nich daje:
Column_1 Column_2 Column_3
3 2 0Tam, gdzie wynik wynosi 0, kolumna składa się całkowicie z wartości NULL.
Wreszcie , jeśli musisz tylko sprawdzić konkretną kolumnę, TOP 1 jest szybszy, ponieważ powinien zatrzymać się przy pierwszym trafieniu. Następnie możesz opcjonalnie użyć funkcji count (*), aby uzyskać wynik typu boolean:
select top 1 'There is at least one NULL' from TestTable where Column_3 is NULL
select count(*) from (select top 1 'There is at least one NULL' AS note from TestTable where Column_3 is NULL) a0 = Nie ma wartości NULL, 1 = Istnieje co najmniej jedna wartość NULL
lub
select top 1 'There is at least one non-NULL' AS note from TestTable where Column_3 is not NULL
select count(*) from (select top 1 'There is at least one non-NULL' AS note from TestTable where Column_3 is not NULL) a0 = Wszystkie mają wartość NULL, 1 = Istnieje co najmniej jedna wartość inna niż NULL
Mam nadzieję, że to pomoże.
UNPIVOT tłumaczy kolumny na wiersze. W procesie eliminuje wartości NULL ( odniesienie ).
Biorąc pod uwagę wkład
create table #t
(
ID int primary key,
c1 int null,
c2 int null
);
insert #t(id, c1, c2)
values
(1, 12, 13),
(2, null, 14),
(3, 15, null),
(4, null, null);zapytanie UNPIVOT
select
ID, ColName, ColValue
from
(
select *
from #t
) as p
unpivot
(
ColValue for ColName in
(c1, c2) -- explicit source column names required
) as unpvt;wytworzy wynik
| ID | ColName | ColValue |
|----|---------|----------|
| 1 | c1 | 12 |
| 1 | c2 | 13 |
| 2 | c2 | 14 |
| 3 | c1 | 15 |Niestety rząd 4 został całkowicie wyeliminowany, ponieważ ma tylko NULL! Można go wygodnie ponownie wprowadzić, wprowadzając wartość fikcyjną do zapytania źródłowego:
select
ID, ColName, ColValue
from
(
select
-5 as dummy, -- injected here, -5 is arbitrary
*
from #t
) as p
unpivot
(
ColValue for ColName in
(dummy, c1, c2) -- referenced here
) as unpvt;Agregując wiersze według identyfikatora, możemy policzyć wartości inne niż null. Porównanie całkowitej liczby kolumn w tabeli źródłowej pozwoli zidentyfikować wiersze zawierające co najmniej jedną wartość NULL.
select
ID
from
(
select -5 as dummy, *
from #t
) as p
unpivot
(
ColValue for ColName in
(dummy, c1, c2)
) as unpvt
group by ID
having COUNT(*) <> 3;
Obliczam 3 jako liczbę kolumn w tabeli źródłowej #t
+ 1 dla wstrzykiwanej kolumny obojętnej
- 1 dla identyfikatora, który nie jest UNPIVOTED
Wartość tę można uzyskać w czasie wykonywania, sprawdzając tabele katalogu.
Oryginalne wiersze można odzyskać, łącząc się z wynikami.
Jeżeli mają być badane wartości inne niż NULL, można je włączyć do klauzuli where:
...
) as unpvt
where ColValue <> '' -- will eliminate empty stringsWymaga to identyfikatora przenoszonego przez UNPIVOT. Klucz byłby najlepszy. Jeśli nie istnieje, można wprowadzić ROW_NUMBER () funkcji okna , choć jego wykonanie może być kosztowne.
Wszystkie kolumny muszą być wyraźnie wymienione w klauzuli UNPIVOT. Można je przeciągać za pomocą SSMS, jak sugeruje @ db2. Nie będzie dynamiczny, gdy zmieni się definicja tabeli, jak sugerowałaby Aaron Bertrand. Jest tak jednak w przypadku prawie wszystkich SQL.
W przypadku mojego raczej ograniczonego zestawu danych plan wykonania to skanowanie indeksu klastrowego i agregacja strumienia. Będzie to droższe w pamięci niż zwykły skan tabeli i wiele klauzul OR.