這題的關鍵在 unpivot 你可以使用UNION ALL 或你的dbms是sql server 你可以使用
CROSS APPLY 和 Values

最後在使用 聚合條件函式 來記數

Query 1:

SELECT v.Field_1,
       v.Field_2,
       v.Field_3,
       COUNT(CASE WHEN v.val IS NULL THEN 1 END) '計算結果'
FROM TestTable t CROSS APPLY (VALUES
  (Field_1,Field_2,Field_3,Field_1),
  (Field_1,Field_2,Field_3,Field_2),
  (Field_1,Field_2,Field_3,Field_3)
) v (Field_1,Field_2,Field_3,val)
GROUP BY v.Field_1,
       v.Field_2,
       v.Field_3

Results:

| Field_1 | Field_2 | Field_3 | 計算結果 |
|---------|---------|---------|------|
|  (null) |  (null) |  (null) |    3 |
|      12 |      13 |      15 |    0 |
|      20 |  (null) |      21 |    1 |

CROSS APPLY...VALUES相關文章

想請問各位，是否有方法可以計算一整個橫列的null個數，現在只想到用case when，可是資料量龐大，不太方便。

可以使用INFORMATION_SCHEMA.COLUMNS配合sp_execute_sql動態查詢某表格所有欄位的null數
之後別的表只要修改@table_name表格名稱,就能查詢它所有的欄位有null值得和

--設定
declare @table_name nvarchar(100) = 'T'; --填寫要查詢的表格名稱
declare @columns table (column_name nvarchar(200));

--保存表格欄位資料
insert into @columns
select T1.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS T1 with (nolock)
left join INFORMATION_SCHEMA.TABLES T2 with (nolock) on T1.TABLE_NAME = T2.TABLE_NAME
where 1 =1  and Table_Type = 'BASE TABLE' and T2.TABLE_NAME = @table_name;

--組合動態SQL
declare @sql nvarchar(max) = 'select ';
select @sql = @sql + column_name + ',' from @columns
select @sql = @sql + ' case when ' + column_name + ' is null then 1 else 0 end +' from @columns

--執行
set @sql =  LEFT(@sql, Len(@sql)-1) + ' as null_count' + ' from ' + @table_name;
exec sp_executesql @sql

線上測試連結

若 欄位1,欄位2,欄位3 同時都不是 null 佔比很高時,可分兩部份做 union ;
case when 只處理一部份

select id,Field_1,Field_2,Field_3,
Field_1T+Field_2T+Field_3T as CountNull
from (
select id,Field_1,Field_2,Field_3,
case when Field_1 is null then 1 else 0 end as Field_1T,
case when Field_2 is null then 1 else 0 end as Field_2T,
case when Field_3 is null then 1 else 0 end as Field_3T
from TestTable
where 1=1
and not (Field_1 is not null
and Field_2 is not null
and Field_3 is not null)
) as TempTable
where 1=1

union
select id,Field_1,Field_2,Field_3,0 as CountNull
from TestTable
where 1=1
and Field_1 is not null
and Field_2 is not null
and Field_3 is not null
order by id

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=2e214e07595a6cd676dc39201dd4c2de

資料庫是MS SQL OR ORACLE...是哪個廠牌?
你可以分別先算出每個欄位的null數量再加總起來。

ISNULL(數字欄位1 * 0, 1) + ISNULL(數字欄位2 * 0, 1) + ISNULL(數字欄位3 * 0, 1)

--or

ISNULL(LEN(文字欄位1) * 0, 1) + ISNULL(LEN(文字欄位2) * 0, 1) + ISNULL(LEN(文字欄位3) * 0, 1) 

-- 使用 PostgreSQL

create table ithelp190524 (
  id serial not null primary key
, col1 int
, col2 int
, col3 int
);

insert into ithelp190524 (col1, col2, col3) values
(20, null, 21),
(null, null, null),
(12, 13, 15);

-----
-- num_nulls(), num_nonnulls()

select *
     , num_nulls(col1, col2, col3)
     , num_nonnulls(col1, col2, col3)
  from ithelp190524;

+----+------+------+------+-----------+--------------+
| id | col1 | col2 | col3 | num_nulls | num_nonnulls |
+----+------+------+------+-----------+--------------+
|  1 |   20 |    ¤ |   21 |         1 |            2 |
|  2 |    ¤ |    ¤ |    ¤ |         3 |            0 |
|  3 |   12 |   13 |   15 |         0 |            3 |
+----+------+------+------+-----------+--------------+
(3 rows)