下面的查询对特定的客户，根据给定的最小总计值（minAmount)，查询出所有未付订单，返回其订单号、货品总数、订单总金额，结果按照总金额排序。在决定价格的时候，参考当前目录。产生的SQL查询，在ORDER,ORDER_LINE,PRODUCT,CATALOG和PRICE表之间有四个内部连接和一个没有产生关联的字查询。

select order.id, sum(price.amount), count(item)
from Order as order
    join order.lineItems as item
    join item.product as product,
    Catalog as catalog
    join catalog.prices as price
where order.paid = false
    and order.customer = :customer
    and price.product = product
    and catalog.effectiveDate < sysdate
    and catalog.effectiveDate >= all (
        select cat.effectiveDate
        from Catalog as cat
        where cat.effectiveDate < sysdate
    )
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc
好家伙，真长！实际上，在现实生活中我并不是非常热衷于子查询，所以我的查询往往是这样的：

select order.id, sum(price.amount), count(item)
from Order as order
    join order.lineItems as item
    join item.product as product,
    Catalog as catalog
    join catalog.prices as price
where order.paid = false
    and order.customer = :customer
    and price.product = product
    and catalog = :currentCatalog
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc
下面的查询统计付款记录处于每种状态中的数量，要排除所有处于AWAITING_APPROVAL状态的，或者最近一次状态更改是由当前用户做出的。它翻译成SQL查询后，在PAYMENT,PAYMENT_STATUS和PAYMENT_STATUS_CHANGE表之间包含两个内部连接和一个用于关联的子查询。

select count(payment), status.name
from Payment as payment
    join payment.currentStatus as status
    join payment.statusChanges as statusChange
where payment.status.name <> PaymentStatus.AWAITING_APPROVAL
    or (
        statusChange.timeStamp = (
            select max(change.timeStamp)
            from PaymentStatusChange change
            where change.payment = payment
        )
        and statusChange.user <> :currentUser
    )
group by status.name, status.sortOrder
order by status.sortOrder
假若我已经把statusChange集合映射为一个列表而不是一个集合的话，查询写起来会简单很多。

select count(payment), status.name
from Payment as payment
    join payment.currentStatus as status
where payment.status.name <> PaymentStatus.AWAITING_APPROVAL
    or payment.statusChanges[ maxIndex(payment.statusChanges) ].user <> :currentUser
group by status.name, status.sortOrder
order by status.sortOrder
下面的查询使用了MS SQL Server的isNull()函数，返回当前用户所属的组织所有账户和未付支出。翻译为SQL查询后，在ACCOUNT, PAYMENT, PAYMENT_STATUS,ACCOUNT_TYPE, ORGANIZATION 和 ORG_USER表之间有三个内部连接，一个外部连接和一个子查询。

select account, payment
from Account as account
    left outer join account.payments as payment
where :currentUser in elements(account.holder.users)
    and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID)
order by account.type.sortOrder, account.accountNumber, payment.dueDate
对某些数据库而言，我们可能不能依赖（关联的）子查询。

select account, payment
from Account as account
    join account.holder.users as user
    left outer join account.payments as payment
where :currentUser = user
    and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID)
order by account.type.sortOrder, account.accountNumber, payment.dueDate
11.13. 提示和技巧（Tips & Tricks）
你不返回结果集也可以查询结果集的大小：

( (Integer) session.iterate("select count(*) from ....").next() ).intValue()
要依据一个集合的大小对结果集排序，可以用下面的查询来对付一对多或多对多的关联：

select usr
from User as usr
    left join usr.messages as msg
group by usr
order by count(msg)
如果你的数据库支持子查询，你可以在查询的where子句中对选择的大小进行条件限制：

from User usr where size(usr.messages) >= 1
如果你的数据库不支持子查询，可以使用下列查询：

select usr.id, usr.name
from User usr.name
    join usr.messages msg
group by usr.id, usr.name
having count(msg) >= 1
因为使用了inner join,这个解决方法不能返回没有message的User.下面的方式就可以：

select usr
from User as usr
    left join usr.messages as msg
group by usr
having count(msg) = 0
JavaBean的属性可以直接作为命名的查询参数：

Query q = s.createQuery("from foo in class Foo where foo.name=:name and foo.size=:size");
q.setProperties(fooBean); // fooBean has getName() and getSize()
List foos = q.list();
在Query接口中使用过滤器(filter),可以对集合分页：

Query q = s.createFilter( collection, "" ); // the trivial filter
q.setMaxResults(PAGE_SIZE);
q.setFirstResult(PAGE_SIZE * pageNumber);
List page = q.list();
集合元素可以使用查询过滤器(query filter)进行排序或者分组：

Collection orderedCollection = s.filter( collection, "order by this.amount" );
Collection counts = s.filter( collection, "select this.type, count(this) group by this.type" );
不用初始化集合就可以得到其大小：

( (Integer) session.iterate("select count(*) from ....").next() ).intValue();