通过多个表循环查询

Question

我想要相同的查询，它几乎永远不会通过多个表返回任何结果。除了添加了年份之外，表名是相同的。所以我的查询如下：

select p.productID, po.name, p.price from prices_2001  p
join products po on p.productID = po.id
where price < 0

我想回顾一下从2001年到2020年的所有年份。所以它应该是这样的：

select p.productID, po.name, p.price from prices_2001  p
join products po on p.productID = po.id
where price < 0
select p.productID, po.name, p.price from prices_2002  p
join products po on p.productID = po.id
where price < 0
select p.productID, po.name, p.price from prices_2003  p    --Looping trough all the tables until 2020
join products po on p.productID = po.id
where price < 0

如果有结果，可以将它们存储在临时表中。我曾想过用表名创建一个循环，或者创建动态sql查询。最佳做法是什么？如何仅执行一次就可以将此查询用于所有表？

谢谢！

Answer 1

重申我的评论：

这听起来像是一个去规范化问题，实际上你应该有一个表，其中包含一年的列。

真的，你应该修复你的设计。

不管怎样，问题是：你可以用动态SQL来做这件事，是的，但你最好修复设计。然而，这会动态地创建和执行该语句。由于缺少版本标签：，我使用了FOR XML PATH

DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = STUFF((SELECT @CRLF + N'UNION ALL' + @CRLF +
N'SELECT p.productID, po.name, p.price' + @CRLF +
N'FROM ' + QUOTENAME(t.[name]) + N'p' + @CRLF +
N'     JOIN product po ON p.productID = po.id' + @CRLF +
N'WHERE p.price < 0'
FROM sys.tables t
WHERE t.[name] LIKE N'prices[_]20[0-9][0-9]'
ORDER BY t.[name]
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,13,N'') + N';';
--PRINT @SQL; --Your debugging friend
EXEC sys.sp_executesql @SQL;

Answer 2

这既困难又昂贵(循环SQL语句！(，因为您的模式不好。您应该有一个与现有表相同的表prices，但有一个称为year的额外列。然后连接就变得简单了，您不必仅仅因为日期更改就创建新的表。

相反，您可以将表联合在一起，得到相同的结果。类似于：

SELECT p.productID, po.name, p.price, po.year
FROM products p
INNER JOIN 
(
SELECT id, name, 2001 as year FROM prices_2001
UNION ALL SELECT id, name, 2002 FROM prices_2002
UNION ALL SELECT id, name, 2003 FROM prices_2003
UNION ALL ... <continue until you have all year tables accounted for>
) po
ON p.productID = po.id
WHERE po.price < 0;

考虑将UNION子查询的结果写入一个新表中，然后从现在开始使用它