需要优化SQL Server查询
问题描述:
我有一个表“N”列 &我必须写一个简单的查询是这样的:需要优化SQL Server查询
SELECT col_1,col_2,col_3,...col_n FROM SingleTableOnly
WHERE col_1 IS NOT NULL
AND col_2 IS NOT NULL
AND col_3 IS NOT NULL
... -- for each column i need to put this condition again & again
AND col_n IS NOT NULL
请建议我为同一更好的逻辑。
还能有这样的:
SELECT ALL COLUMNS FROM SingleTableOnly
WHERE ALL COLUMNS IS NOT NULL
-- i know this isnt right sql query, but trying to figure something where i can write less.
这是特定于MS SQL Server 2005的唯一。
答
如果有很多的列或列的数量是未知的,你可以使用动态SQL来完成这项工作,虽然可能不是最好的办法:
declare @sql varchar(4000)
set @sql = ' where 1=1 '
declare my_cursor cursor
for
SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'tablename'
ORDER BY
ORDINAL_POSITION ASC;
open my_cursor
declare @colname varchar(20)
fetch next from my_cursor into @colname
while(@@FETCH_STATUS <> -1)
begin
set @sql = @sql + ' and ' + @colname + ' is not null '
fetch next from my_cursor into @colname
end
close my_cursor
deallocate my_cursor
--select 'select * from tablename ' + @sql
EXEC('select * from tablename ' + @sql)
答
DECLARE @TableName VARCHAR(100)
SET @TableName = 'Your_Table_Name'
DECLARE @columns VARCHAR(MAX)
SELECT @columns = STUFF((SELECT ' and '+COLUMN_NAME +' IS NOT NULL ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName ORDER BY ORDINAL_POSITION ASC FOR XML PATH('')),1,4,'')
EXEC('SELECT * FROM ' + @TableName ' WHERE ' + @sql)
+0
它可以在没有游标的情况下完成!很高兴知道... – 2011-01-12 07:33:40
答
唯一的办法我可以看到你可以写少一点的代码(与优化无关)不是检查非空列。
如果它是一个谜或测试的问题,你可能会很可能使用的事实,任何+空= NULL(请,不这样做在实际应用中),所以看起来
WHERE ((int_field1+int_field2+ intfield_n) IS NOT NULL)
AND ((string_field1+string_field2+...) IS NOT NULL)
AND ((datetime_field1+datetime_field2+...) IS NOT NULL)
没有其他你正在描述的where子句的方式。 – 2011-01-12 07:01:20
您可以通过编写视图并从中进行选择来绕过这个问题。 – 2011-01-12 07:32:15