我基本上是在尝试使用NHibernate ICriteria接口创建此查询:
I am basically trying to create this query with NHibernate ICriteria interface:
SomeTable 1:n AnotherTable
SomeTable 1:n AnotherTable
SomeTable 具有列:PrimaryKey,NonAggregateColumn AnotherTable 具有以下列:主键,外键,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很明显,使用Projection子查询不是很有效,因为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.
推荐答案不幸的是,条件有所限制.
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.
它应该产生如下内容:
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和filter来制作笛卡尔乘积. (仅适用于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)的子查询的LEFT JOIN
发布评论