时间戳

首页 > 解决方案 > SQL - 使用变量访问数据库

sql - SQL - 使用变量访问数据库

问题描述

我正在尝试访问每个季度在新数据库中重新创建的表。表名保持不变，但会创建一个新的数据库名。我想要实现的是使用 select 语句来包含一个变量，该变量将查看范围为 Q317 到 Q119 的特定数据库。目前我已经设置了选择代码，但我必须重用相同的代码，但要更改每个数据库的季度。有没有我目前正在做的更有效的方法？
为了使事情变得更棘手，Quarter 值必须是字符串而不是整数。

如您所见，我不是专家，我尝试将变量传递给有效的字符串，但我无法成功地让它迭代。

declare @tablename varchar(50)
set @tablename = '2'
EXEC('SELECT DISTINCT [GAELTACHT] FROM [EDB_Q' + @tablename + '18].[dbo].[POSTAL_ADDRESS]')

这是我目前正在使用的简化版本，但只是为了演示我正在使用的数据库。

SELECT 'Q119' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q119
UNION ALL
SELECT 'Q418' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q418
UNION ALL
SELECT 'Q318' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q318 
UNION ALL
SELECT 'Q218' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q218
UNION ALL
SELECT 'Q118' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q118 
UNION ALL
SELECT 'Q417' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q417

理想情况下，我将有 1 个选择语句以某种方式从值 218 到 119 之间进行迭代（但在整数上使用字符串）。我不知道我所要求的是否可能？

标签： sqlsql-servertsql

解决方案

那这个呢？

DECLARE @quarterStart int = 2
DECLARE @quarterEnd int = 1
DECLARE @yearStart int = 18
DECLARE @yearEnd int = 19

DECLARE @year int
DECLARE @quarter int

DECLARE @quarterForDbName nvarchar(4)
DECLARE @sqlStatement nvarchar(max)

SET @year = @yearStart
SET @quarter = @quarterStart
SET @sqlStatement = ''

WHILE @year <= @yearEnd
BEGIN
   WHILE ((@year < @yearEnd AND @quarter <= 4) OR (@year = @yearEnd AND @quarter <= @quarterEnd))
   BEGIN
      SET @quarterForDbName = 'Q' + CAST(@quarter AS nvarchar(1)) + CAST(@year AS nvarchar(2))
      SET @sqlStatement = @sqlStatement + 'SELECT ''' + @quarterForDbName + ''' AS Quarter, Count(BUILDING_ID) FROM Towns.CITY_' + @quarterForDbName + ' UNION ALL '
      SET @quarter = @quarter + 1
   END
   SET @quarter = 1
   SET @year = @year + 1
END

PRINT SUBSTRING(@sqlStatement, 1, LEN(@sqlStatement) - 10)

结果：

SELECT 'Q218' AS Quarter, Count(BUILDING_ID) FROM Towns.CITY_Q218 UNION ALL SELECT 'Q318' AS Quarter, Count(BUILDING_ID) FROM Towns.CITY_Q318 UNION ALL SELECT 'Q418' AS Quarter, Count(BUILDING_ID) FROM Towns.CITY_Q418 UNION ALL SELECT 'Q119' AS Quarter, Count(BUILDING_ID) FROM Towns.CITY_Q119

您可以使用动态 SQL，而不是最后的 PRINT 语句：

EXEC SUBSTRING(@sqlStatement, 1, LEN(@sqlStatement) - 10)

最好的祝愿
迈克尔

推荐阅读