SQL Server - 选择满足特定条件的列？

Question

我的 COLUMNS 只能包含三个值或 var 字符 - economy、basic、luxury。我想选择一个 ROW 并仅显示那些包含奢侈品的 COLUMNS。问题是有很多这样的列——大约 50 个。我不想在我的选择查询中键入所有这些列的名称。有没有更短更简单的替代方案？我应该使用哪个查询？

我在想这样的事情(这是一个假查询)-

@declare Column_Name varchar(30)
select Column_Name where Column_Value = 'luxury' 
from ATable
where rowId = 'row 5';

表结构-

rowId | Column1 | Column2 | Column3.....

Answer 1

我已经为您创建了一个存储过程。

此过程检查 MSSQL 元以构建动态 SQL 字符串，该字符串返回包含列名称 N 及其值 V 以及相应行键 的结果K 从中检索到指定表的值。

执行此操作时，结果存储在名为##ColumnsByValue 的全局临时表中，然后可以直接查询。

通过执行此脚本创建 GetColumnsByValue 存储过程:

-- =============================================
-- Author:      Ben Roberts (sepster@internode.on.net)
-- Create date: 22 Mar 2013
-- Description: Returns the names of columns that contain the specified value, for a given row
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'dbo.GetColumnsByValue', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.GetColumnsByValue;
GO
CREATE PROCEDURE dbo.GetColumnsByValue
    -- Add the parameters for the stored procedure here
    @idColumn sysname,
    @valueToFind nvarchar(255), 
    @dbName sysname,
    @tableName sysname,
    @schemaName sysname,
    @debugMode int = 0

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @SQL nvarchar(max);
    DECLARE @SQLUnion nvarchar(max);
    DECLARE @colName sysname;
    DECLARE @dbContext nvarchar(256);
    DECLARE @Union nvarchar(10);

    SELECT @dbContext = @dbName + '.' + @schemaName + '.sp_executeSQL';
    SELECT @SQLUnion = '';
    SELECT @Union = '';

    IF OBJECT_ID ( 'tempdb..##GetColumnsByValueIgnoreList') IS NULL -- no columns to ingore have been specified, need to create an empty list.
    BEGIN
        CREATE TABLE ##GetColumnsByValueIgnoreList (column_name nvarchar(255));
    END

    DECLARE DBcursor CURSOR FOR
        SELECT 
            COLUMN_NAME
        FROM 
            INFORMATION_SCHEMA.COLUMNS
        WHERE 
            TABLE_NAME = @tableName 
            AND 
            TABLE_SCHEMA = @schemaName;

    OPEN DBcursor; 
        FETCH DBcursor INTO @colName;
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            IF (
                @colName != @idColumn
                AND
                @colName NOT IN (SELECT column_name FROM ##GetColumnsByValueIgnoreList)
            )
            BEGIN
                SELECT @SQL = 'SELECT '+@idColumn+' as K, '''+@colName+''' as N, ' +@colName+ ' as V FROM ' + @dbName + '.' + @schemaName + '.' + @tableName;
                --PRINT @SQL;
                SELECT @SQLUnion = @SQL + @Union + @SQLUnion;
                SELECT @Union = ' UNION ';
            END
            FETCH  DBcursor INTO @colName;
        END; -- while
    CLOSE DBcursor; DEALLOCATE DBcursor;

    IF (@debugMode != 0)
        BEGIN
            PRINT @SQLUnion;
            PRINT @dbContext;
        END
    ELSE
        BEGIN
            -- Delete the temp table if it has already been created.
            IF OBJECT_ID ('tempdb..##ColumnsByValue') IS NOT NULL 
                BEGIN 
                    DROP TABLE ##ColumnsByValue 
                END

            -- Create a new temp table
            CREATE TABLE ##ColumnsByValue (
                K nvarchar(255), -- Key
                N nvarchar(255), -- Column Name
                V nvarchar(255)  -- Column Value
            )

            -- Populate it with the results from our dynamically generated SQL.
            INSERT INTO ##ColumnsByValue EXEC @dbContext @SQLUnion;
        END
END
GO

SP 将多个输入作为参数，这些在以下代码中进行了解释。

另请注意，我提供了一种添加“忽略列表”作为输入的机制:

• 这允许您列出任何不应包含的列名在结果中。
• 您不需要添加用作键的列，即示例结构中的 row_id。
• 您必须包括其他不是 varchar 的列这些将导致错误(因为 SP 只是进行 varchar 比较在它查看的所有列上)。
• 这是通过您必须创建/填充的临时表完成的
• 您的示例表结构表明该表仅包含感兴趣的列，因此这可能不适用于你。

我已经包含了如何执行此操作的示例代码(但只有在您需要时才执行此操作):

IF OBJECT_ID ( 'tempdb..##GetColumnsByValueIgnoreList') IS NOT NULL
    BEGIN
        DROP TABLE ##GetColumnsByValueIgnoreList;
    END
CREATE TABLE ##GetColumnsByValueIgnoreList (column_name nvarchar(255));
INSERT INTO ##GetColumnsByValueIgnoreList VALUES ('a_column');
INSERT INTO ##GetColumnsByValueIgnoreList VALUES ('another_column');
INSERT INTO ##GetColumnsByValueIgnoreList VALUES ('yet_another_column');

现在，要启动构建临时结果表的过程，请使用以下代码(当然还要进行适当的修改)。

-- Build the ##ColumnsByValue table
EXEC dbo.GetColumnsByValue
    @idColumn = 'row_id',   -- The name of the column that contains your row ID (eg probably your PK column)
    @dbName = 'your_db_name',
    @tableName = 'your_table_name',
    @schemaName = 'dbo',
    @debugMode = 0          -- Set this to 1 if you just want a print out of the SQL used to build the temp table, to 0 if you want the temp table populated

这给你留下了 ##ColumnsByValue，你可以在上面执行任何你需要的搜索，例如:

select * from ##ColumnsByValue WHERE v = 'luxury' and k = 5 --some_row_id

您需要为要检查的每个表重新执行存储过程(如果相关，请先创建/修改忽略列表表)。

这种方法的一个问题是在您的情况下可能会超出 nvarchar 长度。你可能会。需要使用不同的数据类型、减少列名长度等。或者将其分解为子步骤并将结果合并在一起以获得您想要的结果集。

我担心的另一个问题是，这对于您的特定场景来说完全是矫枉过正，一次性脚本到查询窗口将为您提供所需内容的基础，然后在 Notepad++ 中进行一些巧妙的文本编辑即可你一直在那里......因此这个问题很可能(并且相当合理地)阻止你这样做!但这是一个很好的一般情况问题，因此值得任何对 future 感兴趣的人回答 ;-)