如何获取列值不为空的行

2021-09-10 00:00:00 sql tsql mysql oracle sql-server

我想获取表中没有列值为空的行.没有对列值进行硬编码.我有数百个列名.

I want to get rows of a table such that no column value is null. No hardcoding of column values. I have hundreds of column names so.

输出应该只有第 2 行，因为所有该行都包含所有列的值.我不想为 is not null 指定所有列名.它应该以编程方式接受它.即使我添加了一个新列，它也应该可以在不更改查询的情况下工作.这是我的愿景.

Output should be only row 2 since all that row has the values for all the columns. I do not want to specify all the column names for is not null. It should take it programmatically. Even if i add a new column it should work without changing the query. That is my vision.

推荐答案

我找到了一些东西，但这意味着使用 CURSOR

I found something, but that means using CURSOR

DECLARE @ColumnName VARCHAR(200)
DECLARE @ColumnCount INT
DECLARE @sql VARCHAR(400)

CREATE TABLE #tempTable (Id INT)

DECLARE GetNonNullRows CURSOR 
FOR 
    SELECT c.NAME, (SELECT COUNT(*) FROM sys.columns col WHERE col.object_id = c.OBJECT_ID)  FROM sys.tables AS t
    JOIN sys.columns AS c ON t.object_id = c.object_id
    WHERE t.name = 'SomeTable' AND t.type = 'U'

OPEN GetNonNullRows
FETCH NEXT FROM GetNonNullRows INTO @ColumnName, @ColumnCount
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'SELECT st.UniqueId FROM SomeTable AS st WHERE ' + CONVERT(varchar, @ColumnName) + ' IS NOT NULL'    
    INSERT INTO #tempTable
    EXEC (@sql)

FETCH NEXT FROM GetNonNullRows INTO @ColumnName, @ColumnCount
END 

CLOSE GetNonNullRows
DEALLOCATE GetNonNullRows

SELECT * FROM SomeTable AS st1
WHERE st1.UniqueId IN (SELECT Id FROM #tempTable AS tt
GROUP BY Id
HAVING COUNT(Id) = @ColumnCount)


DROP TABLE #tempTable

让我稍微解释一下.

首先我创建游标，它遍历一个表的所有列.对于每一列，我创建了 sql 脚本来在表中搜索所选列的非空值.对于那些满足条件的行，我取其唯一 ID 并放入临时表中，我将这项工作用于所有列.

First i create cursor which iterate through all the columns of one table. For each column, I've create sql script to search in table for not null values for selected column. For those rows that satisfies criteria, I take its unique ID and put in temp table, and this job I am using for all columns.

最后，只有像列数一样计数的 ID 才是您的结果集，因为只有具有相同出现次数(如表中的列数)的行才可能是所有列中都包含非空值的行.

At the end only ID's which count is like columns count are your result set, because only rows that have identical number of appearances like number of columns in table may be rows with all non null values in all columns.

相关文章