时间戳

首页 > 解决方案 > 使用动态 SQL 循环访问数据库中的多个 Float 列

sql - 使用动态 SQL 循环访问数据库中的多个 Float 列

问题描述

如何遍历数据库中不同表的多个列？我通过一次查询一列来找到我需要的结果，这需要很多时间。

SELECT MAX
       (CASE Charindex('.', COLUMN1)
                    WHEN 0 THEN 0
                    ELSE
   Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), COLUMN1, 128)
                     ) AS FLOAT) AS BIGINT))
                  END) AS MAX_LENGTH
FROM   DB1.dbo.TABLE1

我的代码在这里，但它不起作用，因为我发送了多个值

DECLARE @cols NVARCHAR(MAX)
    ,@sql NVARCHAR(MAX)

SET @cols = (SELECT quotename(COLUMN_NAME) FROM BRSDATA.INFORMATION_SCHEMA.COLUMNS A
inner join BRSDATA.INFORMATION_SCHEMA.TABLES B
on A.TABLE_NAME = B.TABLE_NAME
where TABLE_TYPE = 'BASE TABLE'
and DATA_TYPE = 'Float')

Set @sql =  'SELECT MAX(CASE Charindex(''.'',' + @cols + ')
                    WHEN 0 THEN 0
                    ELSE
   Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), ' + @cols + ', 128)
                     ) AS FLOAT) AS BIGINT))
                  END) AS MAX_LENGTH'

                  print(@sql)

Msg 512, Level 16, State 1, Line 101
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

我也尝试使用游标和“成功执行的查询”，但即使没有关闭和释放游标，我也看不到结果。

DECLARE @SchemaName SYSNAME = 'dbo'
DECLARE @TableName SYSNAME 
DECLARE @ColumnName SYSNAME


DECLARE FLOAT_COLUMNS CURSOR FOR
SELECT t.name,c.name 
  FROM BRSDATA.sys.tables AS t
  INNER JOIN BRSDATA.sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  inner join BRSDATA.sys.columns AS c
  on c.object_id = t.object_id
  WHERE s.name = 'dbo'
  and type_desc = 'USER_TABLE';

OPEN FLOAT_COLUMNS

FETCH NEXT FROM FLOAT_COLUMNS
INTO @TableName, @ColumnName

    WHILE @@FETCH_STATUS = 0
    BEGIN

       DECLARE @Columns NVARCHAR(MAX)
       SET @Columns = 
       STUFF(
          (SELECT
             ',' + QUOTENAME(name)
          FROM
             sys.columns
          WHERE
             object_id = OBJECT_ID(QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName))
          FOR XML PATH(''))
          ,1,1,'')

        DECLARE @SQL AS NVARCHAR(MAX)
        SET @SQL = 'SELECT' + QUOTENAME(@TableName) + ',' + QUOTENAME(@ColumnName) +  ', MAX(CASE Charindex(''.'',' + @Columns + ')
                    WHEN 0 THEN 0
                    ELSE
   Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), ' + @Columns + ', 128)
                     ) AS FLOAT) AS BIGINT))
                  END) AS MAX_LENGTH'

        --use print to view and copy your dynamic sql string to see if you have formed it correctly
        PRINT @SQL
        --EXECUTE (@SQL)

        FETCH NEXT FROM FLOAT_COLUMNS
        INTO @TableName, @ColumnName
    END

CLOSE FLOAT_COLUMNS
DEALLOCATE FLOAT_COLUMNS

标签： sqlsql-serversql-server-2008-r2

解决方案

不确定这是否是您需要的，但这就是我的做法：

   DECLARE @cols NVARCHAR(MAX)
    ,@table NVARCHAR(MAX)
    ,@sql NVARCHAR(MAX)
    ,@X INT = 1


WHILE @X < (
    SELECT MAX(ROWID) FROM 
    (
        SELECT quotename(COLUMN_NAME) col
        ,ROW_NUMBER()over(ORDER BY COLUMN_NAME) ROWID
        FROM INFORMATION_SCHEMA.COLUMNS A
        inner join INFORMATION_SCHEMA.TABLES B
        on A.TABLE_NAME = B.TABLE_NAME
        where TABLE_TYPE = 'BASE TABLE'
        and DATA_TYPE = 'Float'
    ) AS X
  WHERE ROWID < 10
)
 BEGIN

SET @cols = 
(SELECT col FROM 
(
    SELECT quotename(COLUMN_NAME) col
    ,ROW_NUMBER()over(ORDER BY COLUMN_NAME) ROWID
    FROM INFORMATION_SCHEMA.COLUMNS A
    inner join INFORMATION_SCHEMA.TABLES B
    on A.TABLE_NAME = B.TABLE_NAME
    where TABLE_TYPE = 'BASE TABLE'
    and DATA_TYPE = 'Float'
) AS X
WHERE ROWID = @X 
)

SET @table = 
(SELECT tablename FROM 
(
    SELECT quotename(A.TABLE_NAME) tablename
    ,ROW_NUMBER()over(ORDER BY COLUMN_NAME) ROWID
    FROM INFORMATION_SCHEMA.COLUMNS A
    inner join INFORMATION_SCHEMA.TABLES B
    on A.TABLE_NAME = B.TABLE_NAME
    where TABLE_TYPE = 'BASE TABLE'
    and DATA_TYPE = 'Float'
) AS X
WHERE ROWID = @X 
)


Set @sql =  'SELECT MAX(CASE Charindex(''.'',' + @cols + ')
                    WHEN 0 THEN 0
                    ELSE
        Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), ' + @cols + ', 128)
                     ) AS FLOAT) AS BIGINT))
                  END) AS MAX_LENGTH
        ,MAX(CASE Charindex(''.'',' + @table + ')
                    WHEN 0 THEN 0
                    ELSE
        Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), ' + @table + ', 128)
                     ) AS FLOAT) AS BIGINT))
                  END) AS MAX_LENGTH'

PRINT(@sql)
SET @X = @X +1
END

推荐阅读