問題描述

我想獲取表中沒有列值為空的行.沒有對列值進行硬編碼.我有數百個列名.

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.

這篇關于如何獲取列值不為空的行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！