mysql - 批处理和出价的 SQL 查询 : finding if user has highest bid

Question

我正在构建一个 Laravel 拍卖应用程序。我有一张用于拍品的表格和一张用于出价的表格。

手数表:

mysql> select id, lot_name, created_at, updated_at from lots;
+----+----------+---------------------+---------------------+
| id | lot_name | created_at          | updated_at          |
+----+----------+---------------------+---------------------+
|  1 | Lot 1    | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
|  2 | Lot 2    | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
|  3 | Lot 3    | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
|  4 | Lot 4    | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
|  5 | Lot 5    | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
|  6 | Lot 6    | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
|  7 | Lot 7    | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
|  8 | Lot 8    | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
|  9 | Lot 9    | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
| 10 | Lot 10   | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
| 11 | Lot 11   | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
| 12 | Lot 12   | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
| 13 | Lot 13   | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
| 14 | Lot 14   | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
| 15 | Lot 15   | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
| 16 | Lot 16   | 2016-06-09 14:23:58 | 2016-06-09 14:23:58 |
+----+----------+---------------------+---------------------+

和出价表:

mysql> select * from bids;
+----+--------+---------+-----------+---------------------+---------------------+
| id | lot_id | user_id | bid_value | created_at          | updated_at          |
+----+--------+---------+-----------+---------------------+---------------------+
|  1 |      1 |       2 | 100       | 2016-06-01 11:14:15 | 2016-06-09 14:23:59 |
|  2 |      1 |       2 | 150       | 2016-06-01 13:19:02 | 2016-06-09 14:23:59 |
|  3 |      1 |       3 | 160       | 2016-06-02 09:02:35 | 2016-06-09 14:23:59 |
|  4 |      1 |       2 | 180       | 2016-06-02 12:25:16 | 2016-06-09 14:23:59 |
|  5 |      2 |       2 | 100       | 2016-06-01 09:26:17 | 2016-06-09 14:23:59 |
|  6 |      2 |       3 | 150       | 2016-06-01 10:14:15 | 2016-06-09 14:23:59 |
|  7 |      2 |       2 | 160       | 2016-06-01 11:35:09 | 2016-06-09 14:23:59 |
|  8 |      3 |       3 | 180       | 2016-06-04 11:14:15 | 2016-06-09 14:23:59 |
|  9 |      1 |       3 | 200       | 2016-06-03 19:29:56 | 2016-06-09 14:23:59 |
| 10 |      3 |       2 | 250       | 2016-06-04 23:14:15 | 2016-06-09 14:23:59 |
| 11 |      4 |       2 | 500       | 2016-06-03 09:16:39 | 2016-06-09 14:23:59 |
| 12 |      1 |       3 | 230       | 2016-06-01 11:14:15 | 2016-06-09 14:23:59 |
| 13 |      1 |       2 | 250       | 2016-06-01 11:14:15 | 2016-06-09 14:23:59 |
| 14 |      3 |       2 | 300       | 2016-06-05 02:14:15 | 2016-06-09 14:23:59 |
| 15 |      2 |       3 | 180       | 2016-06-01 20:44:15 | 2016-06-09 14:23:59 |
| 16 |      4 |       2 | 600       | 2016-06-04 08:59:15 | 2016-06-09 14:23:59 |
| 17 |      1 |       3 | 300       | 2016-06-03 17:20:30 | 2016-06-09 14:23:59 |
| 18 |      1 |       3 | 350       | 2016-06-04 18:04:55 | 2016-06-09 14:23:59 |
| 19 |      7 |       2 | 160       | 2016-06-02 22:24:02 | 2016-06-09 14:23:59 |
| 20 |      1 |       3 | 400       | 2016-06-05 07:00:27 | 2016-06-09 14:23:59 |
| 21 |      8 |       3 | 100       | 2016-06-02 11:58:29 | 2016-06-09 14:23:59 |
| 22 |      1 |       3 | 450       | 2016-06-05 10:09:06 | 2016-06-09 14:23:59 |
| 23 |      1 |       2 | 480       | 2016-06-05 14:12:15 | 2016-06-09 14:23:59 |
| 24 |      5 |       2 | 1000      | 2016-06-02 13:38:34 | 2016-06-09 14:23:59 |
| 25 |      1 |       2 | 525       | 2016-06-05 20:39:29 | 2016-06-09 14:23:59 |
| 26 |      1 |       3 | 600       | 2016-06-05 23:59:50 | 2016-06-09 14:23:59 |
| 27 |      6 |       2 | 1000      | 2016-06-01 07:00:02 | 2016-06-09 14:23:59 |
| 28 |      6 |       3 | 1200      | 2016-06-04 23:14:56 | 2016-06-09 14:23:59 |
+----+--------+---------+-----------+---------------------+---------------------+

关系 Lot-Bid:一批可以包含许多投标(来自许多用户)，每个投标只属于一个批处理和一个用户。

我的目标是创建一个 View ，显示用户是否对给定的拍品出价最高。我将在一个带有绿色/红色行颜色的表格中指出这一点(如果用户对该地段的出价最高则为绿色)。

理想情况下我想实现以下目标(这样我就可以创建一个 Laravel 查询并将其发送到 View 以循环遍历数组)

+--------+---------+----------------+----------------+
| lot_id | user_id | max(bid_value) | highest value  |  extra columns from the lots table such as created_at, updated_at
+--------+---------+----------------+----------------+
|      1 |     2   |     525        |    600         |   ....
|      2 |     2   |     160        |    180         |   ....
|      3 |     2   |     300        |    300         |   ....
|      4 |     2   |     600        |    600         |   ....
|      5 |     2   |     1000       |    1000        |   ....
|      6 |     2   |     1000       |    1200        |   ....
|      7 |     2   |     160        |    160         |   ....
+--------+--------------------------+----------------+

我得到以下内容:

mysql> select lot_id, max(bid_value) as 'highest value' from bids group by lot_id;
+--------+---------------+
| lot_id | highest value |
+--------+---------------+
|      1 | 600           |
|      2 | 180           |
|      3 | 300           |
|      4 | 600           |
|      5 | 1000          |
|      6 | 1200          |
|      7 | 160           |
|      8 | 100           |
+--------+---------------+

和:

mysql> select lot_id, max(bid_value) as user_highest_bid from bids where user_id=2 group by lot_id;
+--------+------------------+
| lot_id | user_highest_bid |
+--------+------------------+
|      1 | 525              |
|      2 | 160              |
|      3 | 300              |
|      4 | 600              |
|      5 | 1000             |
|      6 | 1000             |
|      7 | 160              |
+--------+------------------+

我如何将其结合起来以获得我想要的表格，以便包括额外的拍品信息并且仅包括用户出价的这些拍品？

---- 编辑----添加 Mureinik 的正确答案(稍作修改):

mysql> SELECT lots.*, b.*
    ->        FROM   lots
    ->        JOIN   (SELECT   lot_id,
    ->                         2 AS user_id,
    ->                         MAX(bid_value) AS highest_value,
    ->                         MAX(CASE user_id WHEN 2 THEN bid_value END) AS highest_bid
    ->                FROM     bids
    ->                GROUP BY lot_id) b ON lots.id = b.lot_id AND highest_bid IS NOT NULL;

Answer 1

您在这里需要两个查询 - 一个查询拍品的详细信息，一个查询汇总出价。这里的一个巧妙技巧是对特定用户的最高出价使用 case 语句，因此您只需要一个聚合查询。

在此查询中，我假设您只需要用户实际出价的结果。如果这不是真的，只需在连接中删除 not null 条件:

SELECT lot.*, b.*
FROM   lots
JOIN   (SELECT   lot_id, 
                 2 AS user_id, 
                 MAX(bid_value) AS highest_value,
                 MAX(CASE user_id WHEN 2 THEN bid_value END) AS highest_bid
        FROM     bids
        GROUP BY lot_id) b ON lot.lot_id = b.lot_id AND highest_bid IS NOT NULL