NHibernate QueryOver<> -通过SubQuery进行汇总的功能 [英] NHibernate QueryOver<> - Aggregate function over SubQuery

问题描述

如何使用QueryOver<>语法编写以下SQL语句?

How can I write the following SQL statement using QueryOver<> syntax?

SELECT COUNT(*) FROM (
    SELECT FirstName,LastName 
    FROM People 
    GROUP BY FirstName, LastName
    ) as sub_t

到目前为止，我的内部查询一直在工作:

I have the inner query working so far:

var q = _session.QueryOver<Person>()
    .SelectList(l => l
        .SelectGroup(x => x.FirstName)
        .SelectGroup(x => x.LastName));

但是我不知道如何将其包装在子查询中并从中获取行数.能做到吗?

But I have no idea how to wrap this in a subquery and get a row count out of it. Can it be done?

不幸的是，我的RDBMS方言(MsSqlCe40Dialect)不支持COUNT DISTINCT，因此我没有使用SelectCountDistinct()的好处.

Unfortunately my RDBMS dialect (MsSqlCe40Dialect) does not support COUNT DISTINCT so I do not have the benefit of using SelectCountDistinct().

推荐答案

我对QueryOver并不熟悉，但是当无法对这种类型的计数进行子查询时，我使用了以下聚合函数，认为这可能有用，并且在发布时发现了一些我以前不知道的问题，所以我也发布了它们.

I am not familiar with QueryOver, but I have used the following aggregate function when a sub query was not possible for this type of count, thought it might be useful, and while posting discovered a few issues I wasn't aware of previously so I posted them too.

注意:数据量适中时，速度要慢10倍左右.

Note: it is about 10x slower with moderate data amounts.

汇总方法

SELECT
COUNT(DISTINCT FirstName+LastName )
FROM People

适应特殊情况

类似的组合名称乔·史密斯"和乔·米斯" (假设〜不在您的数据集中)

similar combination names "Joe Smith" vs "Joes Mith" (Assumes ~ is not in your dataset)

SELECT
COUNT(DISTINCT FirstName+'~'+LastName )
FROM People

空 (假设^不在您的数据集中)

nulls (Assumes ^ is not in your dataset)

SELECT
COUNT(DISTINCT IsNull(FirstName,'^')+'~'+IsNull(LastName,'^') )
FROM People

跟踪空白，似乎RTRIM是Group By固有的

Trailing white space, seems RTRIM is intrinsic to Group By

SELECT
COUNT(DISTINCT IsNull(RTrim(FirstName),'^')+'~'+IsNull(Rtrim(LastName),'^') )
FROM People

基准化 (AMD单四核上有8万行数据)

Benchmarking (80k rows of data on AMD single Quad Core)

80-100ms-运行子查询方法(请参阅OP)

80-100ms - run Sub Query Method (see OP)

800-1200ms-具有特殊情况的不同方法的聚合方法似乎没有什么明显的区别.

800-1200ms - aggregate method with distinct, accommodating for special cases doesn't seem to make much noticeable difference.

这篇关于NHibernate QueryOver&lt;&gt; -通过SubQuery进行汇总的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！