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 好家伙，真長(zhǎng)！實(shí)際上，在現(xiàn)實(shí)生活中我并不是非常熱衷于子查詢，所以我的查詢往往是這樣的：
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 下面的查詢統(tǒng)計(jì)付款記錄處于每種狀態(tài)中的數(shù)量，要排除所有處于AWAITING_APPROVAL狀態(tài)的，或者最近一次狀態(tài)更改是由當(dāng)前用戶做出的。它翻譯成SQL查詢后，在PAYMENT,PAYMENT_STATUS和PAYMENT_STATUS_CHANGE表之間包含兩個(gè)內(nèi)部連接和一個(gè)用于關(guān)聯(lián)的子查詢。
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 假若我已經(jīng)把statusChange集合映射為一個(gè)列表而不是一個(gè)集合的話，查詢寫起來會(huì)簡(jiǎn)單很多。
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()函數(shù)，返回當(dāng)前用戶所屬的組織所有賬戶和未付支出。翻譯為SQL查詢后，在ACCOUNT, PAYMENT, PAYMENT_STATUS,ACCOUNT_TYPE, ORGANIZATION 和 ORG_USER表之間有三個(gè)內(nèi)部連接，一個(gè)外部連接和一個(gè)子查詢。
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 對(duì)某些數(shù)據(jù)庫而言，我們可能不能依賴（關(guān)聯(lián)的）子查詢。
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 Top
from eg.Cat as cat 上面的語句為Cat賦予了一個(gè)別名cat 。所以后面的查詢可以用這個(gè)簡(jiǎn)單的別名了。as關(guān)鍵字是可以省略的，我們也可以寫成這樣：
from eg.Cat cat 可以出現(xiàn)多個(gè)類，結(jié)果是它們的笛卡爾積，或者稱為“交叉”連接。
from Formula, Parameter from Formula as form, Parameter as param 讓查詢中的別名服從首字母小寫的規(guī)則，我們認(rèn)為這是一個(gè)好習(xí)慣。這和Java對(duì)局部變量的命名規(guī)范是一致的。(比如，domesticCat).
from eg.Cat as cat inner join cat.mate as mate left outer join cat.kittens as kitten
from eg.Cat as cat left join cat.mate.kittens as kittens
from Formula form full join form.parameter param 支持的連接類型是從ANSI SQL借用的：
內(nèi)連接，inner join
左外連接，left outer join
右外連接，right outer join
全連接，full join (不常使用)
inner join, left outer join 和 right outer join 都可以簡(jiǎn)寫。
from eg.Cat as cat join cat.mate as mate left join cat.kittens as kitten 并且，加上 "fetch"后綴的抓取連接可以讓聯(lián)合的對(duì)象隨著它們的父對(duì)象的初始化而初始化，只需要一個(gè)select語句。這在初始化一個(gè)集合的時(shí)候特別有用。它有效地覆蓋了映射文件中對(duì)關(guān)聯(lián)和集合的外連接定義。
from eg.Cat as cat inner join fetch cat.mate left join fetch cat.kittens 抓取連接一般不需要賦予別名，因?yàn)楸宦?lián)合的對(duì)象應(yīng)該不會(huì)在where子句（或者任何其它子句）中出現(xiàn)。并且，被聯(lián)合的對(duì)象也不會(huì)在查詢結(jié)果中直接出現(xiàn)。它們是通過父對(duì)象進(jìn)行訪問的。
請(qǐng)注意，目前的實(shí)現(xiàn)中，在一次查詢中只會(huì)抓取一個(gè)集合（其他的一切都做不到。）（？原文為：only one collection role may be fetched in a query）。也請(qǐng)注意，在使用scroll()或者 iterate()方式調(diào)用的查詢中，是禁止使用fetch構(gòu)造的。最后，請(qǐng)注意full join fetch和right join fetch是沒有意義的。
select mate from eg.Cat as cat inner join cat.mate as mate 這個(gè)查詢會(huì)選擇出作為其它貓（Cat）朋友（mate）的那些貓。當(dāng)然，你可以更加直接的寫成下面的形式：
select cat.mate from eg.Cat cat 你甚至可以選擇集合元素，使用特殊的elements功能。下面的查詢返回所有貓的小貓。
select elements(cat.kittens) from eg.Cat cat 查詢可以返回任何值類型的屬性，包括組件類型的屬性：
select cat.name from eg.DomesticCat cat where cat.name like ‘fri%‘
select cust.name.firstName from Customer as cust 查詢可以用元素類型是Object[]的一個(gè)數(shù)組返回多個(gè)對(duì)象和/或多個(gè)屬性。
select mother, offspr, mate.name from eg.DomesticCat as mother inner join mother.mate as mate left outer join mother.kittens as offspr 或者實(shí)際上是類型安全的Java對(duì)象
select new Family(mother, mate, offspr) from eg.DomesticCat as mother join mother.mate as mate left join mother.kittens as offspr 上面的代碼假定Family有一個(gè)合適的構(gòu)造函數(shù)。
select avg(cat.weight), sum(cat.weight), max(cat.weight), count(cat) from eg.Cat cat 在select子句中，統(tǒng)計(jì)函數(shù)的變量也可以是集合。
select cat, count( elements(cat.kittens) ) from eg.Cat cat group by cat 下面是支持的統(tǒng)計(jì)函數(shù)列表：
avg(...), sum(...), min(...), max(...)
count(*)
count(...), count(distinct ...), count(all...)
distinct 和 all關(guān)鍵字的用法和語義與SQL相同。
select distinct cat.name from eg.Cat cat
select count(distinct cat.name), count(cat) from eg.Cat cat 11.6. 多態(tài)(polymorphism)查詢 類似下面的查詢：
from eg.Cat as cat 返回的實(shí)例不僅僅是Cat，也有可能是子類的實(shí)例，比如DomesticCat。Hibernate查詢可以在from子句中使用任何Java類或者接口的名字。查詢可能返回所有繼承自這個(gè)類或者實(shí)現(xiàn)這個(gè)接口的持久化類的實(shí)例。下列查詢會(huì)返回所有的持久化對(duì)象：
from java.lang.Object o 可能有多個(gè)持久化類都實(shí)現(xiàn)了Named接口：
from eg.Named n, eg.Named m where n.name = m.name 請(qǐng)注意，上面兩個(gè)查詢都使用了超過一個(gè)SQL的SELECT。這意味著order by子句將不會(huì)正確排序。（這也意味著你不能對(duì)這些查詢使用Query.scroll()。）
11.7. where子句 where子句讓你縮小你要返回的實(shí)例的列表范圍。
from eg.Cat as cat where cat.name=‘Fritz‘ 返回所有名字為‘Fritz‘的Cat的實(shí)例。
select foo from eg.Foo foo, eg.Bar bar where foo.startDate = bar.date 會(huì)返回所有的滿足下列條件的Foo實(shí)例，它們存在一個(gè)對(duì)應(yīng)的bar實(shí)例，其date屬性與Foo的startDate屬性相等。復(fù)合路徑表達(dá)式令where子句變得極為有力。思考下面的例子：
from eg.Cat cat where cat.mate.name is not null 這個(gè)查詢會(huì)被翻譯為帶有一個(gè)表間（inner)join的SQL查詢。如果你寫下類似這樣的語句：
from eg.Foo foo where foo.bar.baz.customer.address.city is not null 你最終會(huì)得到的查詢，其對(duì)應(yīng)的SQL需要4個(gè)表間連接。
=操作符不僅僅用于判斷屬性是否相等，也可以用于實(shí)例：
from eg.Cat cat, eg.Cat rival where cat.mate = rival.mate
select cat, mate from eg.Cat cat, eg.Cat mate where cat.mate = mate 特別的，小寫的id可以用來表示一個(gè)對(duì)象的惟一標(biāo)識(shí)。（你可以使用它的屬性名。）
from eg.Cat as cat where cat.id = 123
from eg.Cat as cat where cat.mate.id = 69 第二個(gè)查詢是很高效的。不需要進(jìn)行表間連接！
from eg.Cat cat where cat.class = eg.DomesticCat 你也可以指定組件（或者是組件的組件，依次類推）或者組合類型中的屬性。但是在一個(gè)存在路徑的表達(dá)式中，最后不能以一個(gè)組件類型的屬性結(jié)尾。（這里不是指組件的屬性）。比如，假若store.owner這個(gè)實(shí)體的的address是一個(gè)組件
from eg.AuditLog log, eg.Payment payment where log.item.class = ‘eg.Payment‘ and log.item.id = payment.id 注意上面查詢中，log.item.class和payment.class會(huì)指向兩個(gè)值，代表完全不同的數(shù)據(jù)庫字段。
