具有内部联接的SQL max() [英] SQL max() with inner joins

问题描述

我正在创建一个拍卖网站.人们可以在这里寻找物品并在其上下注.我希望在用户帐户区域中有一个列表，显示用户对其进行出价的所有项目.

I am creating an auction website. Here people can look for items and place bets on them. In the user account area I wish to have a list showing all the items where the user has placed a bid on.

当然，每个项目都可以有来自不同用户的多个下注，所以我只希望显示最高金额的一个.这样一来，用户就可以跟踪对其出价的所有项目，并可以跟踪他是否仍然是出价最高的项目.

Ofcourse each item can have more than one bet from different users so I only wish to show the one with the highest amount of money. This is so that the user can follow all the items on which he placed a bid on, and can track if he is still the one with the highest amount or not.

这是我的数据库的样子:

This is how my database looks like:

Tabel item                      Tabel itembid                          Tabel user
+=========================+     +================================+     +==============+
|id | title | description |     |id | item_id | user_id | amount |     |id | username |
+=========================+     +================================+     +==============+
| 1 | item1 | ........... |1   *| 1 |    1    |    2    |   10   |*   1| 1 |    me    |
| 2 | item2 | ........... |-----| 2 |    1    |    1    |   15   |-----| 2 |  myself  |
| 3 | item3 | ........... |     | 3 |    2    |    3    |   5    |     | 3 |    I     |
+=========================+     | 4 |    2    |    1    |   10   |     +==============+
                                +================================+

因此，如上所示，我有3个表(item，itembid和user).如您所见，用户我"设置了2个出价，一次在项目1上，一次在项目2上.事实证明，他目前也是这两个项目上出价最高的人.

So as shown above, I have 3 tables (item, itembid and user). As you can see user 'me' has placed 2 bid, once on item 1 and once on item 2. It turns out that he is also currently the one with the highest bids on both items.

现在，用户我自己"对项目1进行了出价，而我"对项目2进行了出价.但是，它们不再是出价最高的用户(用户我"是).现在，我需要一条SQL语句，该语句为我提供信息列表(标题，描述，金额，用户名)，该信息列表是我曾经出价的所有项目的列表.如果我当前是该项目的最高出价者，则需要查看该项目的标题和说明以及我下注的金额和用户名.现在，如果我不是出价最高的用户，我仍然想查看该项目的信息，但现在要查看出价最高的用户的数量和用户名.

Now user 'myself' placed a bid before on item1 and 'I' placed a bid on item2. However, they are not anymore the ones with the highest bid (user 'me' is). Now I need an SQL statement that gives me a list of information (title, description, amount, username) that is a list of all items where I once placed a bid on. If I am the one currently with the highest bid for that item, I need to see the title and description of the item together with the amount that I placed for my bet as well as my username. Now, if I am not the one with the highest bid, I stil want to see the information of that item but now with the amount and username of the one with the highest bid.

一个例子，从用户我"的角度来看，我想看看:

So an example, looking in the perspective of user 'me', I want to see:

> item1, 15, me
> item2, 10, me

现在我希望看到用户我自己":

Now for user 'myself' I wish to see:

> item1, 15, me

(因为我"曾经为item1出价，但不再是出价最高的用户，因此用户我"就是这样)

(since 'me' once placed a bid for item1 but is no longer the one with highest amount, user 'me' is)

到目前为止，这是我的工作，但是运行起来并不安静...

This is what I have so far but isn't working quiet well...

SELECT i.title, i.description, ib.amount, u.username
FROM item i
INNER JOIN itembid ib ON i.id = ib.item_id
INNER JOIN user u ON ib.user_id = u.id
WHERE ib.amount = (SELECT max(amount) FROM itembid ib2 WHERE ib2.id = ib.id)
AND ib.user_id = 1

推荐答案

在这里是

SELECT i.title, bmax.amount, u.username
FROM itembid AS b
JOIN (SELECT item_id, MAX(amount) AS amount
      FROM itembid
      GROUP BY item_id) AS bmax
    ON b.item_id = bmax.item_id AND b.amount = bmax.amount
JOIN item AS i ON i.id = b.item_id
JOIN itembid AS ubid ON ubid.item_id = i.item_id
JOIN user AS u ON u.id = b.user_id
WHERE ubid.user_id = :current_user

这篇关于具有内部联接的SQL max()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！