问题描述
我使用的是 SQL Server 2012.我有一个包含 90 列的表.我试图只选择包含数据的列.搜索后,我使用了以下程序:
I am using SQL Server 2012. i have a table with 90 columns. I am trying to select only columns that contains data. After searching i used the following procedure:
1- 使用一个选择查询获取所有列数
1- Getting all columns count using one select query
2- 将结果表转为临时表
2- Pivoting Result Table into a Temp table
3- 创建选择查询
4- 执行此查询
这是我使用的查询:
DECLARE @strTablename varchar(100) = 'dbo.MyTable'
DECLARE @strQuery varchar(max) = ''
DECLARE @strSecondQuery varchar(max) = 'SELECT '
DECLARE @strUnPivot as varchar(max) = ' UNPIVOT ([Count] for [Column] IN ('
CREATE TABLE ##tblTemp([Column] varchar(50), [Count] Int)
SELECT @strQuery = ISNULL(@strQuery,'') + 'Count([' + name + ']) as [' + name + '] ,' from sys.columns where object_id = object_id(@strTablename) and is_nullable = 1
SELECT @strUnPivot = ISNULL(@strUnPivot,'') + '[' + name + '] ,' from sys.columns where object_id = object_id(@strTablename) and is_nullable = 1
SET @strQuery = 'SELECT [Column],[Count] FROM ( SELECT ' + SUBSTRING(@strQuery,1,LEN(@strQuery) - 1) + ' FROM ' + @strTablename + ') AS p ' + SUBSTRING(@strUnPivot,1,LEN(@strUnPivot) - 1) + ')) AS unpvt '
INSERT INTO ##tblTemp EXEC (@strQuery)
SELECT @strSecondQuery = @strSecondQuery + '[' + [Column] + '],' from ##tblTemp WHERE [Count] > 0
DROP TABLE ##tblTemp
SET @strSecondQuery = SUBSTRING(@strSecondQuery,1,LEN(@strSecondQuery) - 1) + ' FROM ' + @strTablename
EXEC (@strSecondQuery)
问题是这个查询太慢了.有没有最好的方法来实现这一目标?
The problem is that this query is TOO SLOW. Is there a best way to achieve this?
注意事项:
- 表在主键列
ID
上只有一个聚集索引,不包含任何其他索引. - 表格不可编辑.
- 表格包含非常大的数据.
- 执行查询大约需要 1 分钟
- Table have only one clustered index on primary key Column
ID
and does not contains any other indexes. - Table is not editable.
- Table contains very large data.
- Query is taking about 1 minute to be executed
提前致谢.
推荐答案
我不知道这是否更快,但您可能会使用一个技巧:FOR XML AUTO
将忽略没有内容的列:
I do not know if this is faster, but you might use one trick: FOR XML AUTO
will ommit columns without content:
DECLARE @tbl TABLE(col1 INT,col2 INT,col3 INT);
INSERT INTO @tbl VALUES (1,2,NULL),(1,NULL,NULL),(NULL,NULL,NULL);
SELECT *
FROM @tbl AS tbl
FOR XML AUTO
这是结果:col3
丢失...
This is the result: col3
is missing...
<tbl col1="1" col2="2" />
<tbl col1="1" />
<tbl />
知道了这一点,你可以找到所有行中不为NULL的列的列表,如下所示:
Knowing this, you could find the list of columns, which are not NULL in all rows, like this:
DECLARE @ColList VARCHAR(MAX)=
STUFF
(
(
SELECT DISTINCT ',' + Attr.value('local-name(.)','nvarchar(max)')
FROM
(
SELECT
(
SELECT *
FROM @tbl AS tbl
FOR XML AUTO,TYPE
) AS TheXML
) AS t
CROSS APPLY t.TheXML.nodes('/tbl/@*') AS A(Attr)
FOR XML PATH('')
),1,1,''
);
SELECT @ColList
@ColList
的内容现在是 col1,col2
.您可以将此字符串放置在动态创建的 SELECT
中.
The content of @ColList
is now col1,col2
. This string you can place in a dynamically created SELECT
.
将 SELECT *
替换为从 INFORMATION_SCHEMA.COLUMNS
创建的列列表(排除所有 not-nullable)会非常聪明.并且 - 如果需要和可能 - 包含非常大的数据 (BLOB) 的类型.
It would be very clever, to replace the SELECT *
with a column list created from INFORMATION_SCHEMA.COLUMNS
excluding all not-nullable. And - if needed and possible - types, wich contain very large data (BLOBs).
不知道你的非常大的数据实际上意味着什么......只是在一个大约有 500.000 行(使用 SELECT *
)的表上试了一下,结果正确返回不到一分钟后.希望,这足够快...
Don't know what your very large data means actually... Just tried this on a table with about 500.000 rows (with SELECT *
) and it returned correctly after less than one minute. Hope, this is fast enough...
这篇关于使用 SQL Server 选择非空列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!