我是SQL新手,我在解决这个问题时遇到了一些困难。
我想编写select查询,它返回所有那些有空值的行。
我在桌面上有50多列,并且可以添加一些额外的列,因为这样我就很难写出条件。
我们可以使用AFAIK is null但我不想重复它那么多列。
请帮我解决它。 让我知道是否需要任何其他信息。
I new to SQL, I am having some difficulty solving this problem.
I want to write the select query which returns all those rows which have the null value in it.
I have more than 50 columns on the table and can be added some extra column it and because of that I getting difficult to write where condition.
AFAIK we can use is null but I don't want to repeat it for that much columns.
Please help me solve it. Let me know if any additional information required.
最满意答案
你可以试试这个动态的SQL查询。 如果任何列包含空值,则此查询将返回行。
DECLARE @tb NVARCHAR(255) = N'dbo.[tablename]'; 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) AND [is_nullable]=1; EXEC sp_executesql @sql;You can try this dynamic SQL query. This query will return row if any column contain null value.
DECLARE @tb NVARCHAR(255) = N'dbo.[tablename]'; 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) AND [is_nullable]=1; EXEC sp_executesql @sql;更多推荐
发布评论