UNION ALL 将跨公司的数据与具有相同表达式的表组合起来

发布于 2025-01-10 23:59:04 字数 643 浏览 3 评论 0原文

是否有可能组合/联合来自多个表的数据，并在 SELECT 语句中以相同字符结尾？

我们有几家公司，使用相同的表格建立，并且表格中的列设置也相同。对于我来说，不要制作一个包含所有商店的 UNION all 语句，我想知道是否可以制作一个脚本来为我完成此操作。

表格示例：

[Database].[dbo].[Company1$Sales Line]
[Database].[dbo].[Company2$Sales Line]
[Database].[dbo].[Company3$Sales Line]...

我今天如何编写脚本：

SELECT *
FROM [Database].[dbo].[Company1$Sales Line]
UNION ALL
SELECT *
FROM [Database].[dbo].[Company2$Sales Line]
UNION ALL
SELECT *
FROM [Database].[dbo].[Company3$Sales Line]...

我认为有一个更简单的解决方案可以做到这一点。可能是一个 WHILE 循环语句 - 但我不知道什么是最佳实践，以及是否可能。否则我应该在 Excel 中制作一个 VBA 来帮助我做到这一点。

提前致谢：）

原文

Is there by any chance a possibility to combine/union data from multiple tables, ending on the same characters in a SELECT statement?

We have several companies, built up with the same tables, and also the same column setup within the tables. For me to not making a UNION all statement, that includes all of the stores, I would like to know, if I could make a script, which does this for me.

Example of tables:

[Database].[dbo].[Company1$Sales Line]
[Database].[dbo].[Company2$Sales Line]
[Database].[dbo].[Company3$Sales Line]...

How I write the script today:

SELECT *
FROM [Database].[dbo].[Company1$Sales Line]
UNION ALL
SELECT *
FROM [Database].[dbo].[Company2$Sales Line]
UNION ALL
SELECT *
FROM [Database].[dbo].[Company3$Sales Line]...

I would think that there is an easier solution to do this.
Could probably be a WHILE loop statement - but I have no idea, what is the best practice, and if it's even possible. Otherwise I should make a VBA in Excel to assist me in doing this.

Thanks in advance :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

像极了他 2025-01-17 23:59:04

如果您正在寻找一种即时生成所需 SQL 的方法，您可以使用以下内容作为基础，或许可以构建您可以查询的视图内容。另请注意，通常，在连接到相关数据库时，您不会指定 3 个部分 名称。当然，对于多个数据库来说这是不同的，但鉴于您的示例数据，情况似乎并非如此。

select String_Agg(Concat('select * from ', QuoteName(Schema_Name(schema_id)), '.', QuoteName(name)), ' union all' + Char(13))
from sys.tables
where schema_id=Schema_Id('dbo') and name like 'Company%$Sales Line';

If you're after a way to generate the SQL you need on the fly, you can use the following as a basis, perhaps to build the content of a view which you can then query. Also note that generally, you don't specify 3 part names as you connect to the database in question. It's different of course for multiple databases, but that doesn't seem to be the case given your sample data.

select String_Agg(Concat('select * from ', QuoteName(Schema_Name(schema_id)), '.', QuoteName(name)), ' union all' + Char(13))
from sys.tables
where schema_id=Schema_Id('dbo') and name like 'Company%$Sales Line';

回复收藏 0 原文

~没有更多了~