0

## SQL計算同一橫列的null數

20 null 21 1
null null null 3
12 13 15 0

### 6 個回答

1

iT邦研究生 2 級 ‧ 2019-05-24 09:37:57

CROSS APPLYValues

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
| Field_1 | Field_2 | Field_3 | 計算結果 |
|---------|---------|---------|------|
|  (null) |  (null) |  (null) |    3 |
|      12 |      13 |      15 |    0 |
|      20 |  (null) |      21 |    1 |

CROSS APPLY...VALUES相關文章

4

iT邦大師 1 級 ‧ 2019-05-24 09:06:01

--設定
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
rogeryao
iT邦高手 1 級 ‧ 2019-05-24 09:27:07

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
0
rainnet
iT邦新手 5 級 ‧ 2019-05-24 09:37:00

2
niodoruku
iT邦新手 5 級 ‧ 2019-05-24 11:06:58
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)
1

iT邦大師 1 級 ‧ 2019-05-24 15:30:37
-- 使用 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)