因此,我使用的是dvdrental DB,我希望在2017年获得最高收入产生员工,指示每个store . 问题是,我得到了所有的员工和他们的收入,但我只需要一个顶级员工每个store . 我也被限制在使用窗口函数.
以下是我的疑问
SELECT staff.first_name,
staff.last_name,
address.address,
SUM (payment.amount) AS revenue_generated
FROM payment
INNER JOIN staff ON staff.staff_id = payment.staff_id
INNER JOIN store ON store.store_id = staff.store_id
INNER JOIN address ON address.address_id = store.address_id
WHERE EXTRACT(YEAR FROM payment.payment_date) = 2017
GROUP BY staff.staff_id, address.address_id
ORDER BY revenue_generated DESC;
结果是
Hanna Carry 28 MySQL Boulevard 79736.45
Hanna Rainbow 47 MySakila Drive 40537.94
Peter Lockyard 47 MySakila Drive 40077.97
Jon Stephens 28 MySQL Boulevard 33927.04
Mike Hillyer 47 MySakila Drive 33489.47
所以我有点希望它是这样的,不使用limit:
Hanna Carry 28 MySQL Boulevard 79736.45
Hanna Rainbow 47 MySakila Drive 40537.94