在 MS Reporting Services 中执行动态子查询的最佳方法？

发布于 2024-07-04 09:53:22 字数 463 浏览 6 评论 0原文

我是 SQL Server Reporting Services 的新手，想知道执行以下操作的最佳方法：

查询以获取热门 ID 列表
对每个项目进行子查询以从另一个表获取属性

理想情况下，最终的报告列将如下所示：

[ID] [property1] [property2] [SELECT COUNT(*)
                              FROM AnotherTable 
                              WHERE ForeignID=ID]

可能有多种方法可以构建一个巨大的 SQL 查询来一次性完成这一切，但我' d 更喜欢将其划分。推荐的方法是编写 VB 函数来对每行执行子查询吗？谢谢你的帮助。

原文

I'm new to SQL Server Reporting Services, and was wondering the best way to do the following:

Query to get a list of popular IDs
Subquery on each item to get properties from another table

Ideally, the final report columns would look like this:

[ID] [property1] [property2] [SELECT COUNT(*)
                              FROM AnotherTable 
                              WHERE ForeignID=ID]

There may be ways to construct a giant SQL query to do this all in one go, but I'd prefer to compartmentalize it. Is the recommended approach to write a VB function to perform the subquery for each row? Thanks for any help.

分享到QQ

分享到微博

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

发布评论

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

梦纸 2024-07-11 09:53:22

根据您希望输出的外观，可以使用子报表，或者您可以对 ID、property1、property2 进行分组，并将其他表中的项目显示为详细项目（假设您想要显示的不仅仅是计数）。

像@Carlton Jenke 这样的东西

select t1.ID, t1.property1, t1.property2, t2.somecol, t2.someothercol
from table t1 left join anothertable t2 on t1.ID = t2.ID

我认为你会发现外连接比你给出的示例中的相关子查询有更好的性能。请记住，需要对每一行运行子查询。

Depending on how you want the output to look, a subreport could do, or you could group on ID, property1, property2 and show the items from your other table as detail items (assuming you want to show more than just count).

Something like

select t1.ID, t1.property1, t1.property2, t2.somecol, t2.someothercol
from table t1 left join anothertable t2 on t1.ID = t2.ID

@Carlton Jenke I think you will find an outer join a better performer than the correlated subquery in the example you gave. Remember that the subquery needs to be run for each row.

回复收藏 0 原文

余生再见 2024-07-11 09:53:22

我建议使用子报告。您可以将子报表放置在表格单元格中。

回复收藏 0 原文

千と千尋 2024-07-11 09:53:22

最简单的方法是这样的：

select *,
 (select count(*) from tbl2 t2 where t2.tbl1ID = t1.tbl1ID) as cnt
from tbl1 t1

这是一个可行的版本（使用表变量）：

declare @tbl1 table
(
 tbl1ID int,
 prop1 varchar(1),
 prop2 varchar(2)
)

declare @tbl2 table
(
 tbl2ID int,
 tbl1ID int
)

select *,
 (select count(*) from @tbl2 t2 where t2.tbl1ID = t1.tbl1ID) as cnt
from @tbl1 t1

显然这只是一个原始示例 - 标准规则适用，例如不要选择 * 等...

从 2008 年 8 月 21 日 21 日更新： 27：
@AlexCuse - 是的，完全同意表演。

我开始用外连接编写它，但后来在他的示例输出中看到了计数，并认为这就是他想要的，如果表是外连接的，则计数将无法正确返回。更不用说连接可能会导致您的记录成倍增加（tbl1 中的 1 个条目与 tbl2 中的 2 个条目匹配 = 2 个返回），这可能是意外的。

所以我想这实际上可以归结为您的查询需要返回的具体内容。

2008 年 8 月 21 日 22:07 更新：
回答你问题的其他部分——VB 函数是正确的选择吗？不，绝对不。不是为了这么简单的事情。

函数的性能非常糟糕，返回集中的每一行都会执行该函数。

如果您想“划分”查询的不同部分，则必须更像存储过程一样处理它。构建临时表，执行部分查询并将结果插入表中，然后执行所需的任何进一步查询并更新原始临时表（或插入更多临时表）。

Simplest method is this:

select *,
 (select count(*) from tbl2 t2 where t2.tbl1ID = t1.tbl1ID) as cnt
from tbl1 t1

here is a workable version (using table variables):

declare @tbl1 table
(
 tbl1ID int,
 prop1 varchar(1),
 prop2 varchar(2)
)

declare @tbl2 table
(
 tbl2ID int,
 tbl1ID int
)

select *,
 (select count(*) from @tbl2 t2 where t2.tbl1ID = t1.tbl1ID) as cnt
from @tbl1 t1

Obviously this is just a raw example - standard rules apply like don't select *, etc ...

UPDATE from Aug 21 '08 at 21:27:
@AlexCuse - Yes, totally agree on the performance.

I started to write it with the outer join, but then saw in his sample output the count and thought that was what he wanted, and the count would not return correctly if the tables are outer joined. Not to mention that joins can cause your records to be multiplied (1 entry from tbl1 that matches 2 entries in tbl2 = 2 returns) which can be unintended.

So I guess it really boils down to the specifics on what your query needs to return.

UPDATE from Aug 21 '08 at 22:07:
To answer the other parts of your question - is a VB function the way to go? No. Absolutely not. Not for something this simple.

Functions are very bad on performance, each row in the return set executes the function.

If you want to "compartmentalize" the different parts of the query you have to approach it more like a stored procedure. Build a temp table, do part of the query and insert the results into the table, then do any further queries you need and update the original temp table (or insert into more temp tables).

回复收藏 0 原文

~没有更多了~