代码之家 › 专栏 › 技术社区 › sh4rk

根据任何表中的值选择列(列数可变)SQL

dynamic-sql sql-server-2008 sql

sh4rk · 技术社区 · 10 年前

我有下表:

Table 1

我只想显示至少有一个值低于50的列:

Table 2

我需要一个执行此操作的存储过程。诀窍是,我想在多个表上使用它,但列的数量可能因表而异。

这能做到吗?

1 回复 | 直到 10 年前

Zhang Peng 10 年前

希望这能有所帮助。

SET NOCOUNT  ON 

DECLARE 
    @tablename VARCHAR(50) = 'Table1',
    @valuetocompare INT = 50,
    @otherfields VARCHAR(100) = 'Date, Hour,';

DECLARE @t AS TABLE (cname VARCHAR(10), cvalue INT)
DECLARE @sql NVARCHAR(1000);
DECLARE @cname VARCHAR(128);
DECLARE c CURSOR  
FOR
    SELECT NAME
    FROM   sys.[columns] AS c
    WHERE  c.[object_id] = OBJECT_ID(@tablename)
;

OPEN c;
FETCH NEXT  FROM c INTO @cname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql =  'select ''' + @cname  + ''', ' + @cname + ' from '  + @tablename;

    INSERT INTO @t
      (
        cname,
        cvalue
      )
    EXECUTE (@sql);

    FETCH NEXT FROM c  INTO @cname;
END

CLOSE c;
DEALLOCATE c;

DECLARE @cnames VARCHAR(100) = '';
WITH dcnames AS (
         SELECT DISTINCT cname
         FROM   @t
         WHERE  cvalue < @valuetocompare
     )
SELECT @cnames = @cnames + cname +  ','
FROM   dcnames;

IF @cnames =  ''
    PRINT 'No column value is less than ' + CAST(@valuetocompare AS VARCHAR);
ELSE
BEGIN
      SET @sql =  'select ' + @otherfields  + LEFT(@cnames, LEN(@cnames) - 1)  + ' from ' + @tablename;
      EXECUTE (@sql);
END

推荐文章