在UNION T-SQL中使用循环 [英] Using loop with UNION t-sql

问题描述

我需要用相似的数据查询几个表，但是我不想写冗长的UNION语句.此外，表的数量可以变化.目前，我使用Coldfusion通过以下方式完成此操作:

I have a need to query several tables, with similar data, but I don't want to write a lengthy UNION statement. Also, the number of tables can vary. Currently I accomplish this using Coldfusion by:

1. 遍历表名列表
2. 在循环内执行UNION

赞

set i = 0
set table_suffixes = "blue,green,red,yellow"
loop list="table_suffixes" index="idx_suffix"
   set tName = "table_" & idx_suffix
   if i > 0
     UNION
   end if
   select *
   FROM tName
end loop

这可以在T-SQL中完成吗?我还找不到任何有关使用列表控制循环的文章.我能找到的最接近的是将查询放入循环中.像这样

Can this be done in T-SQL? I have not been able to find any articles about using a list to control a loop. The closest I could find would be to place a query in the loop. Like This

While (SELECT table_suffix FROM vendor_name WHERE discipline = 'electrical')
BEGIN 
  *union query...*
END

任何帮助我指出正确方向的帮助，我们深表感谢.我正在使用SQL-Server 2008

Any help to point me in the right direction is much appreciated. I am using SQL-Server 2008

推荐答案

尝试一下-

查询:

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
    SELECT CHAR(13) + 'UNION ALL' + CHAR(13) + 'SELECT * FROM [' + s.name + '].[' + o.name + ']'
    FROM sys.objects o 
    JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] = 'U'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '')

PRINT @SQL

输出:

SELECT * FROM [dbo].[test1]
UNION ALL
SELECT * FROM [dbo].[MyTable]
UNION ALL
...

在您的情况下，您会看到类似这样的内容-

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
    SELECT CHAR(13) + 'UNION ALL' + CHAR(13) + 'SELECT * FROM ' + table_suffix
    FROM vendor_name 
    WHERE discipline = 'electrical'
    WHERE o.[type] = 'U'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '')

PRINT @SQL

这篇关于在UNION T-SQL中使用循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！