SQL Server如何只选择一列存在于表中
问题描述:
我想执行一个SELECT,它只在该列存在的情况下选择列值,否则显示为空。SQL Server如何只选择一列存在于表中
这是目前我在做什么:
SELECT TOP 10 CASE WHEN EXISTS
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' and COLUMN_NAME='columnName')
THEN columnName ELSE NULL END AS columnName
我也试过这样:
SELECT TOP 10 CASE WHEN
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName' and COLUMN_NAME='columnName') >0
THEN columnName ELSE NULL END AS columnName
他们都工作得很好,如果列表中存在。但是,当该列是不存在的,它给我的错误:
无效列名 '列名'
答
可以作为写:
SELECT CASE WHEN EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName'
and COLUMN_NAME='columnName'
)
THEN
(
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName'
and COLUMN_NAME='columnName'
)
ELSE
NULL
END
AS columnName
编辑: 如果你正在寻找如果该列存在,则从表格列中选择前10个值,则需要编写如下动态查询:
SELECT @columnVariable =
CASE WHEN EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName'
and COLUMN_NAME='columnName'
)
THEN
(
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA ='test' and TABLE_NAME='tableName'
and COLUMN_NAME='columnName'
)
ELSE
NULL
END
/* Build the SQL string one time.*/
SET @SQLString =
N'SELECT TOP 10 ' + @columnVariable+ '
FROM test.tableName ';
EXECUTE sp_executesql @SQLString
+0
感谢Deepkshikah – Ocelot 2014-10-08 05:29:39
答
试试这个:
SELECT
TOP 1
CASE
WHEN COLUMN_NAME='columnName' THEN
COLUMN_NAME
ELSE
NULL
END
AS COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA ='test' and TABLE_NAME='tableName'
ORDER BY
COLUMN_NAME DESC
答
SELECT *
FROM sys.columns
WHERE [name] = N'columnName'
AND [object_id] = OBJECT_ID(N'tableName')
添加这是你的case语句里面。请注意,此代码只工作了更高版本的SQLSERVER的[像SQLSERVER 2008]
您需要使用动态SQL。对于任何特定的查询,它将访问的表和列是固定的,并且如果查询不能编译,查询甚至不会开始执行和检索数据(在你的尝试中需要这些数据)。 – 2014-10-07 07:14:25