SQL 如何检测和删除仅包含空值的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1665868/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to detect and remove a column that contains only null values?
提问by Himadri
In my table table1there are 6 columns Locations,a,b,c,d,e.
在我的表table1 中有 6 列 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
i need the result like
我需要这样的结果
Locations [a] [b] [d]
[1] 10.00 Null 20.00
[2] Null 30.00 Null
My question is how to detect and delete column that contains all null values using sql query. Is it possible?
我的问题是如何使用 sql 查询检测和删除包含所有空值的列。是否可以？
If yes then please help and give sample.
如果是，那么请帮助并提供样品。
采纳答案by onedaywhen
How to detect whether a given column has only the NULLvalue:
如何检测给定列是否只有NULL值：
SELECT 1 -- no GROUP BY therefore use a literal
FROM Locations
HAVING COUNT(a) = 0
AND COUNT(*) > 0;
The resultset will either consist of zero rows (column ahas a non-NULLvalue) or one row (column ahas only the NULLvalue). FWIW this code is Standard SQL-92.
结果集将由零行（列a具有非NULL值）或一行（列a只有NULL值）组成。FWIW 此代码是标准 SQL-92。
回答by SWeko
Here is a fast (and ugly) stored proc that takes the name of the table and print (or drop if you want it to) the fields that are full of nulls.
这是一个快速（且丑陋）的存储过程，它采用表的名称并打印（或删除，如果您愿意）充满空值的字段。
ALTER procedure mysp_DropEmptyColumns
@tableName nvarchar(max)
as begin
declare @FieldName nvarchar(max)
declare @SQL nvarchar(max)
declare @CountDef nvarchar(max)
declare @FieldCount int
declare fieldNames cursor local fast_forward for
select c.name
from syscolumns c
inner join sysobjects o on c.id=o.id
where o.xtype='U'
and o.Name=@tableName
open fieldNames
fetch next from fieldNames into @FieldName
while (@@fetch_status=0)
begin
set @SQL=N'select @Count=count(*) from "'+@TableName+'" where "'+@FieldName+'" is not null'
SET @CountDef = N'@Count int output';
exec sp_executeSQL @SQL, @CountDef, @Count = @FieldCount output
if (@FieldCount=0)
begin
set @SQL = 'alter table '+@TableName+' drop column '+@FieldName
/* exec sp_executeSQL @SQL */
print @SQL
end
fetch next from fieldNames into @FieldName
end
close fieldNames
end
This uses a cursor, and is a bit slow and convoluted, but I suspect that this is a kind of procedure that you'll be running often
这使用了一个游标，有点慢而且有点复杂，但我怀疑这是一种你会经常运行的过程
回答by paxdiablo
SQL is more about working on rowsrather than columns.
SQL 更多的是处理行而不是列。
If you're talking about deleting rows where c is null, use:
如果您要删除 c 为空的行，请使用：
delete from table1 where c is null
If you're talking about dropping a column when all rows have null for that column, I would just find a time where you could lock out the DB from users and execute one of:
如果您正在谈论在该列的所有行都为空时删除一列，我只会找个时间您可以锁定用户的数据库并执行以下操作之一：
select c from table1 group by c
select distinct c from table1
select count(c) from table1 where c is not null
Then, if you only get back just NULL (or 0 for that last one), weave your magic (the SQL Server command maybe different):
然后，如果您只返回 NULL（或最后一个为 0），请使用您的魔法（SQL Server 命令可能不同）：
alter table table1 drop column c
Do this for whatever columns you want.
对您想要的任何列执行此操作。
You really need to be careful if you're deleting columns. Even though they may be full of nulls, there may be SQL queries out there that use that column. Dropping the column will break those queries.
如果您要删除列，您真的需要小心。即使它们可能充满空值，也可能存在使用该列的 SQL 查询。删除列将破坏这些查询。
回答by mjv
SELECT * FROM table1 WHERE c IS NOT NULL -- or also SELECT COUNT(*)
To detect if indeed this column has no values at all.
检测该列是否确实没有任何值。
ALTER TABLE table1 DROP COLUMN c
is the query to remove the column if it is deemed desirable.
是在认为需要时删除列的查询。
回答by Raja A
Try this stored procedure with your table name as input.
尝试使用您的表名作为输入的存储过程。
alter proc USP_DropEmptyColumns
@TableName varchar(255)
as
begin
Declare @col varchar(255), @cmd varchar(max)
DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = @TableName
OPEN getinfo
FETCH NEXT FROM getinfo into @col
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM [' + @TableName + '] WHERE [' + @col + '] IS NOT NULL)
BEGIN
ALTER TABLE [' + @TableName + '] DROP Column [' + @col + ']
end'
EXEC(@cmd)
FETCH NEXT FROM getinfo into @col
END
CLOSE getinfo
DEALLOCATE getinfo
end
回答by BOB AGNEW
PROC PRINT DATA=TABLE1;RUN;
PROC TRANSPOSE DATA=TABLE1 OUT=TRANS1;VAR A B C D E;RUN;
DATA TRANS2;SET TRANS1;IF COL1 = . AND COL2 = . THEN DELETE;RUN;
PROC TRANSPOSE DATA=TRANS2 OUT=TABLE2 (DROP=_NAME_);VAR COL1-COL2;RUN;
PROC PRINT DATA=TABLE2;RUN;
