如何检测和删除仅包含空值的列? [英] How to detect and remove a column that contains only null values?

问题描述

在我的表 table1 中有 6 列 Locations,a,b,c,d,e.

In my table table1 there are 6 columns Locations,a,b,c,d,e.

Locations [a]   [b]   [c]  [d]   [e]

[1]       10.00 Null  Null 20.00 Null

[2]       Null  30.00 Null Null  Null

我需要这样的结果

Locations [a]   [b]   [d]

[1]       10.00 Null  20.00

[2]       Null  30.00 Null

我的问题是如何使用 sql 查询检测和删除包含所有空值的列.可能吗?

My question is how to detect and delete column that contains all null values using sql query. Is it possible?

如果是，请帮助并提供样品.

If yes then please help and give sample.

推荐答案

如何检测给定的列是否只有 NULL 值:

How to detect whether a given column has only the NULL value:

SELECT 1  -- no GROUP BY therefore use a literal
  FROM Locations
HAVING COUNT(a) = 0 
       AND COUNT(*) > 0;

结果集将由零行(a 列具有非NULL 值)或一行(a 列只有NULL 值).FWIW 此代码是标准 SQL-92.

The resultset will either consist of zero rows (column a has a non-NULL value) or one row (column a has only the NULL value). FWIW this code is Standard SQL-92.

这篇关于如何检测和删除仅包含空值的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！