使用 SQL Server 选择非空列 [英] Select non-empty columns using SQL Server

问题描述

我使用的是 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 选择非空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！