急问 hibernate query - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

急问 hibernate query

急问 hibernate query# Database - 数据库

o*s2006-08-18 07:08

1 楼

I have a table called customers (id(primary key), customer name, purchase
date, and receipt number(foreign key))
and
another table called purchases (receipt number(primary key), details, amount
)
the mapping is done correctly (many to one) and all the other easy hql runs
just fine
I want to find out the most recent purchases for each customer with the
amount shown. So, basically, the result is a list of unique customers,
sorted by purchase date, and grouped by customers name, with the purchase
a

k*e2006-08-18 07:08

2 楼

if it's MS SQL, and Customername is unique and no more than 1 purcghase on a
single day for each customer
select C.CustomerName, C.PurchaseDate, P.Amount
FROM Customers C
JOIN Purchases P
ON C.Receipt#=P.Receipt#
JOIN (SELECT CustomerName, Max(PurchaseDate) as MaxPDtae FROM CUSTOMERS
GROUP BY CUstomerName
) C1 ON C.CustomerName=C1.CustomerName AND C.PurchaseDate=C1.MaxPDate

amount
runs

【在 o********s 的大作中提到】

: I have a table called customers (id(primary key), customer name, purchase
: date, and receipt number(foreign key))
: and
: another table called purchases (receipt number(primary key), details, amount
: )
: the mapping is done correctly (many to one) and all the other easy hql runs
: just fine
: I want to find out the most recent purchases for each customer with the
: amount shown. So, basically, the result is a list of unique customers,
: sorted by purchase date, and grouped by customers name, with the purchase