我基本上是在尝试使用 NHibernate ICriteria 接口创建此查询:
I am basically trying to create this query with NHibernate ICriteria interface:
SomeTable 1:n 另一个表
SomeTable 1:n AnotherTable
SomeTable 具有列:PrimaryKey、NonAggregateColumnAnotherTable 包含以下列:PrimaryKey、ForeignKey、AnotherNonAggregate、YetAnotherNonAggregate
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很明显,使用投影子查询效率不高,因为 SQL 必须扫描表两次(每个聚合一个投影子查询).
It is clear that using Projection subquery is not very efficient, since SQL has to scan the table twice (one projection subquery per aggregate).
使用多个 GROUP BY 也效率不高.
Using multiple GROUP BYs is not efficient as well.
有解决办法吗?到目前为止,我一直在使用原始 SQL,但这对于复杂的报告来说变得很笨拙.
Is there a solution for this ? So far I've been resorting to using raw SQL but this is getting unwieldy for complex reports.
推荐答案很遗憾,Criteria 有点受限.
Unfortunately, Criteria is a bit restricted.
试试这个:
session.CreateCriteria(typeof(SomeTable), "st") .SetProjection( Projections.ProjectionList() .Add(Projections.GroupProperty("st.id")) .Add(Projections.GroupProperty("st.NonAggregateColumn")) .Add(Projections.RowCount(), "rowcount") .Add(Projections.Avg("at.YetAnotherNonAggregate"), "avg")); .CreateCriteria( "st.OtherTables", "at", JoinType.InnerJoin) .List<object[]>();您可能需要玩一下,这更像是一种猜测.这种方式也可能是不可能的.
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:
select st.id, st.NonAggregateColumn, count() as "rowcount", avg(at.YetAnotherNonAggregate) as "avg" from SomeTable st inner join AnotherTable at on ... group by st.id, st.NonAggregateColumn一般:
- 您可以使用 DetachedCriteria 进行子查询.请参阅 文档了解更多详情.
- 您不能在 where 子句中使用 Criteria 和过滤器制作笛卡尔积.(这只适用于 HQL).
- 不能将子查询添加到 from 子句(因为这会产生笛卡尔积).您只能将它们放在 where 子句中(in、exists 等)
- 您可能从 AnotherTable 开始并导航到 SomeTable.这可能是另一种解决方案.
- You can make subqueries using DetachedCriteria. See the docs for more details.
- You can't make a cartesian product with Criteria and filter in the where clause. (This only works with HQL).
- Subqueries can not be added to the from clause (because that would result in a cartesian product). You can only put them to the where clause (in, exists etc.)
- You could probably start with AnotherTable and navigate to SomeTable. This might be a alternative solution.
更多推荐
NHibernate 2.1:在带有别名的子查询上左连接(ICriteria)
发布评论