问题:SQL 获取每个服务员最大账单的 id

我正在使用 PostgreSQL 数据库并有两个表

服务员(id,姓名)

账单(id,金额,id_waiter)

我正在寻找每个服务员最大金额的账单ID。

我找到了以下解决方案

SELECT waiter.id AS waiter, maxamount, bills.id AS bill
FROM waiter
JOIN (
  SELECT id_waiter, max(amount) AS maxamount
  FROM bills
  GROUP BY id_waiter) AS maxis ON maxis.id_waiter = waiter.id
JOIN bills ON maxis.maxamount = bills.amount AND waiter.id =     bills.id_kellner

它有效,但似乎有点多余,我想知道是否有更好的方法来做到这一点。我不喜欢的是 bills 加入了两次,一次在子查询中,一次在末尾。

这是一些示例数据

样本数据

服务员表

id | name
1 | john
2 | joe

账单表

id | amount | id_waiter
1 | 20 | 1
2 | 25 | 2
3 | 50 | 2
4 | 20 | 1
5 | 60 | 1
6 | 10 | 2

结果看起来像这样

waiter | maxamount | bill
1 | 60 | 5
2 | 50 | 3

解答

在 Postgres 中,您可以使用DISTINCT ON:

select distinct on(id_waiter) id_waiter, amount max_amount, id bill
from bills
order by 1, 2 desc;

 id_waiter | max_amount | bill 
-----------+------------+------
         1 |         60 |    5
         2 |         50 |    3
(2 rows)    

事实上,如果你也想选择服务员的名字,join是必要的:

select id_waiter, name, max_amount, bill
from (
    select distinct on(id_waiter) id_waiter, amount max_amount, id bill
    from bills
    order by 1, 2 desc
    ) sub
join waiters w on w.id = id_waiter;

 id_waiter | name | max_amount | bill 
-----------+------+------------+------
         1 | john |         60 |    5
         2 | joe  |         50 |    3
(2 rows)    

SqlFiddle