SQL循环以显示结果
SQL循环以显示结果
提问于 2013-11-06 21:12:00
是否可以在不创建表的情况下循环使用Server中的多个数据库？在运行下面的查询之前，我必须创建一个表。我想在不每次创建表的情况下运行这个查询，并在一个网格中显示结果。我试着用go替换insert into WSA.dbo.TEST，但错误被删除了。还有另一种循环方式不需要我创建一个表并在一个网格上显示我所有的结果吗？
回答 2
Stack Overflow用户
回答已采纳
发布于 2013-11-06 21:21:41
好吧，我没有看到您实际上正在返回或显示任何结果，但是下面是一种更简单的方法来完成您的代码当前所做的工作(您可以填写...详细信息)：
DECLARE @x TABLE(db SYSNAME);
INSERT @x VALUES('Hs'),('Ge'),('At'),('LW'),('SE'),('LL');
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'INSERT WSA.dbo.TEST(column list please)
SELECT ptno, MAX ... FROM (SELECT d.*, row_number() ...
FROM ' + db + '_Active.dbo.DTLPAYMENTS AS d) sq
GROUP BY ptno, cd, rn;
' FROM @x;
PRINT @sql;
-- EXEC sp_executesql @sql;但是，在重新阅读之后，您必须将最终的SELECT排除在代码之外--我认为重点是插入到表中，而不仅仅是在一个网格中显示结果。如果你想避开这张桌子，你可以说：
DECLARE @x TABLE(db SYSNAME);
INSERT @x VALUES('Hs'),('Ge'),('At'),('LW'),('SE'),('LL');
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'UNION ALL
SELECT ptno, MAX ... FROM (SELECT d.*, row_number() ...
FROM ' + db + '_Active.dbo.DTLPAYMENTS AS d) sq
GROUP BY ptno, cd, rn
' FROM @x;
SET @sql = STUFF(@sql, 1, 11, '');
PRINT @sql;
-- EXEC sp_executesql @sql;此查询的输出(如这个SQLfiddle所示)如下：
SELECT ptno, MAX ... FROM (SELECT d.*, row_number() ...
FROM Hs_Active.dbo.DTLPAYMENTS AS d) sq
GROUP BY ptno, cd, rn
UNION ALL
SELECT ptno, MAX ... FROM (SELECT d.*, row_number() ...
FROM Ge_Active.dbo.DTLPAYMENTS AS d) sq
GROUP BY ptno, cd, rn
UNION ALL
SELECT ptno, MAX ... FROM (SELECT d.*, row_number() ...
FROM At_Active.dbo.DTLPAYMENTS AS d) sq
GROUP BY ptno, cd, rn
UNION ALL
SELECT ptno, MAX ... FROM (SELECT d.*, row_number() ...
FROM LW_Active.dbo.DTLPAYMENTS AS d) sq
GROUP BY ptno, cd, rn
UNION ALL
SELECT ptno, MAX ... FROM (SELECT d.*, row_number() ...
FROM SE_Active.dbo.DTLPAYMENTS AS d) sq
GROUP BY ptno, cd, rn
UNION ALL
SELECT ptno, MAX ... FROM (SELECT d.*, row_number() ...
FROM LL_Active.dbo.DTLPAYMENTS AS d) sq
GROUP BY ptno, cd, rn当你填写..。部件，应该在所有6个数据库中生成一个有效的UNION ALL查询。如果您在PRINT输出中没有得到这个结果，那么我怀疑您将查询转录错了，或者您将无效的内容放入了.零件。
STUFF()函数的存在只是为了在执行之前删除第一个UNION ALL子句。
当然，在这个结果中，您不会知道哪些行来自哪个数据库。如果需要，可以添加：
SELECT @sql += N'UNION ALL
SELECT ''' + db + ''', ptno, MAX ... FROM (SELECT d.*, row_number() ...
FROM ' + db + '_Active.dbo.DTLPAYMENTS AS d) sq
GROUP BY ptno, cd, rn;
' FROM @x;Stack Overflow用户
发布于 2013-11-06 21:23:49
为了尽可能少地修改代码逻辑，下面是我要做的事情，以避免使用永久表.
USE WSA
Delete From TEST
GO
DECLARE @Database varchar(20),
@i int = 0,
@SQL Nvarchar(2000)
Declare @table Table
(ptno Int, --Or whatever datatype it should be
dr_cd Int, --Or whatever datatype it should be
dr_amt Int, --Or whatever datatype it should be
dr_desc Int, --Or whatever datatype it should be
cr_cd Int, --Or whatever datatype it should be
cr_amt Int, --Or whatever datatype it should be
cr_desc Int) --Or whatever datatype it should be
WHILE @i < 6
BEGIN
if @i = 0 SELECT @Database = 'Hs_Active'
if @i = 1 SELECT @Database = 'Ge_Active'
if @i = 2 SELECT @Database = 'At_Active'
if @i = 3 SELECT @Database = 'LW_Active'
if @i = 4 SELECT @Database = 'SE_Active'
if @i = 5 SELECT @Database = 'LL_Active'
Set @SQL = 'USE '+@Database +'
insert into WSA.dbo.TEST
Select ptno,
max(case sign(amt) when 1 then cd end) dr_cd,
max(case sign(amt) when 1 then amt end) dr_amt,
max(case sign(amt) when 1 then description end) dr_desc,
max(case sign(amt) when -1 then cd end) cr_cd,
max(case sign(amt) when -1 then amt end) cr_amt,
max(case sign(amt) when -1 then description end) cr_desc
from (select d.*,
row_number() over (partition by ptno, cd, sign(amt)
order by abs(amt)) rn
from DTLPAYMENTS d) sq
group by ptno, cd, rn'
Insert @table
Exec sp_executeSQL @SQL
SELECT @i=@i+1
END
-- Whatever you want to do with @table aka results 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19822906
复制相关文章
相似问题
腾讯云开发者
