NHibernate 2.1:使用别名对子查询进行 LEFT JOIN (ICriteria)
我基本上尝试使用 NHibernate ICriteria 接口创建此查询:
SomeTable 1:n AnotherTable
SomeTable has columns: PrimaryKey, NonAggregateColumn
AnotherTable 具有以下列:PrimaryKey、ForeignKey、AnotherNonAggregate、YetAnotherNonAggregate
SELECT
table1.NonAggregateColumn,
subquery.SubQueryAggregate1,
subquery.SubQueryAggregate2
FROM
SomeTable AS table1
LEFT JOIN
(
SELECT
table2.ForeignKey,
COUNT(table2.AnotherNonAggregate) AS SubQueryAggregate1,
AVG(table2.YetAnotherNonAggregate) AS SubQueryAggregate2
FROM AnotherTable AS table2
GROUP BY (table2.ForeignKey)
) AS subquery ON subquery.ForeignKey = table1.PrimaryKey
显然,使用投影子查询效率不高,因为 SQL 必须扫描表两次(每个聚合一个投影子查询)。
使用多个 GROUP BY 也效率不高。
有解决办法吗?到目前为止,我一直在诉诸使用原始 SQL,但这对于复杂的报告来说变得很笨拙。
I am basically trying to create this query with NHibernate ICriteria interface:
SomeTable 1:n AnotherTable
SomeTable has columns: PrimaryKey, NonAggregateColumn
AnotherTable has columns: PrimaryKey, ForeignKey, AnotherNonAggregate, YetAnotherNonAggregate
SELECT
table1.NonAggregateColumn,
subquery.SubQueryAggregate1,
subquery.SubQueryAggregate2
FROM
SomeTable AS table1
LEFT JOIN
(
SELECT
table2.ForeignKey,
COUNT(table2.AnotherNonAggregate) AS SubQueryAggregate1,
AVG(table2.YetAnotherNonAggregate) AS SubQueryAggregate2
FROM AnotherTable AS table2
GROUP BY (table2.ForeignKey)
) AS subquery ON subquery.ForeignKey = table1.PrimaryKey
It is clear that using Projection subquery is not very efficient, since SQL has to scan the table twice (one projection subquery per aggregate).
Using multiple GROUP BYs is not efficient as well.
Is there a solution for this ? So far I've been resorting to using raw SQL but this is getting unwieldy for complex reports.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
不幸的是,标准有点受限。
试试这个:
你可能需要尝试一下,这更多的是猜测。这也可能是不可能的。
它应该生成如下内容:
一般情况下:
DetachedCriteria
进行子查询。请参阅文档了解更多详细信息。in
、exists
等)。AnotherTable
开始并导航到SomeTable
。这可能是一个替代解决方案。Unfortunately, Criteria is a bit restricted.
Try this:
You probably need to play around a bit, it's more of a guess. It also might be impossible this way.
It should produce something like this:
Generally:
DetachedCriteria
. See the docs for more details.in
,exists
etc.)AnotherTable
and navigate toSomeTable
. This might be a alternative solution.