SQL 从任意数量的相同表中选择记录
我正在尝试查询具有相同结构和相关名称的多个表的数据库（SQLServer），即
[TABLE 01 $TRANSACTIONS]
[TABLE 02 $TRANSACTIONS]
...
[TABLE (n) $TRANSACTIONS]
我有一个查询一次返回一个表上所需的记录，并且可以使用
SELECT {QUERY01} FROM [TABLE 01 $TRANSACTIONS]
UNION
SELECT {QUERY02} FROM [TABLE 02 $TRANSACTIONS]
...
SELECT {QUERY(n)} FROM [TABLE (n) $TRANSACTIONS]
我将其替换为的通用查询 手动选择多个表所需的表名大约为 200 行，涉及 ROWNUMBER()/PARTITION BY 函数、与相关表的多个联接以及一些排序。
随着时间的推移，将添加新表，并且n将发生变化。
任何人都可以建议一种方法，从所有 n 表中为任意 n 值选择记录的 UNION 吗？
注意：通过查询 sysobjects 表可以轻松获得 n 个表的列表
SELECT Name FROM sysobjects
WHERE Type = 'U'
AND Name LIKE '%$TRANSACTIONS'
ORDER BY Name
I am trying to query a database (SQLServer) with multiple tables of identical structure and with related names i.e.
[TABLE 01 $TRANSACTIONS]
[TABLE 02 $TRANSACTIONS]
...
[TABLE (n) $TRANSACTIONS]
I have a query returning the desired records on one table at a time and can manually select multiple tables with
SELECT {QUERY01} FROM [TABLE 01 $TRANSACTIONS]
UNION
SELECT {QUERY02} FROM [TABLE 02 $TRANSACTIONS]
...
SELECT {QUERY(n)} FROM [TABLE (n) $TRANSACTIONS]
The generic query into which I substitute the required table name is approx 200 lines, involving a ROWNUMBER()/PARTITION BY function, multiple joins to related tables and some ordering.
Over time, new tables will be added and n will change.
Can anyone suggest a way to select the UNION of records from all n tables for arbitrary values of n?
Note: the list of n tables can be easily obtained with a query on the sysobjects table
SELECT Name FROM sysobjects
WHERE Type = 'U'
AND Name LIKE '%$TRANSACTIONS'
ORDER BY Name
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
AFAIK，您最好的选择是使用 sysobjects 查询定期生成新的视图定义。
您也许能够创建一个 DDL 触发器，当您的表发生更改时，它会运行重新生成此视图的过程——我真的不知道。像这样的数据库设计简直就是一场灾难。
AFAIK, your best bet is to use your sysobjects query to generate a new view definition periodically.
You might be able to create a DDL trigger which runs the procedure to re-generate this view when your tables change -- I don't really know. DB designs like this are a trainwreck.
您可以通过 sysobjects 查询进行游标并构建 sql 语句。然后你可以调用sp_executesql来运行它。根据经验，我可以告诉您，这些调试起来很痛苦。我还预计该解决方案会随着任何供应商升级而崩溃。祝你好运。
You could cursor through your sysobjects query and construct the sql statement. Then you can call sp_executesql to run it. From experience, I can tell you that these are a pain in the A$$ to debug. I would also expect this solution to fall apart with any vendor upgrade. Good luck.