主页 / 问题 / 687496
Accepted
Vyacheslav Potseluyko
Asked:2020-07-05 17:27:11 +0000 UTC2020-07-05 17:27:11 +0000 UTC

MySQL：从每个组中获取最大价值

  • 772

测试结构： 

CREATE TABLE b (
  id   INT,
  data TEXT
);

CREATE TABLE s (
  id   INT,
  b_id INT,
  time INT
);

INSERT INTO b VALUES (1, 2);
INSERT INTO b VALUES (2, 3);

INSERT INTO s VALUES (1, 1, 3);
INSERT INTO s VALUES (2, 1, 2);
INSERT INTO s VALUES (3, 1, 9);
INSERT INTO s VALUES (4, 2, 8);
INSERT INTO s VALUES (5, 2, 4);
INSERT INTO s VALUES (6, 2, 5);

一个任务：

在一个请求中，对于每个从具有最大值的字段id中b获取的值stime

这里的预期输出：

+------+------+------+------+-----------+
| id   | data | b_id | sid  |    time   |
+------+------+------+------+-----------+
|    1 | 2    |    1 |    3 |         9 |
|    2 | 3    |    2 |    4 |         8 |
+------+------+------+------+-----------+

（列名不是必需的。提供了一个示例以简化对任务的理解）

我自己能做的（好吧，我试过了）

select b.id as bid, b.data, s.time, s.b_id from b b inner join s s on b.id=s.b_id order by s.time DESC;
+------+------+------+------+
| bid  | data | time | b_id |
+------+------+------+------+
|    1 | 2    |    9 |    1 |
|    2 | 3    |    8 |    2 |
|    2 | 3    |    5 |    2 |
|    2 | 3    |    4 |    2 |
|    1 | 2    |    3 |    1 |
|    1 | 2    |    2 |    1 |
+------+------+------+------+
6 rows in set (0.00 sec)

重要，启用模式预计在 subdsql_mode=only_full_group_by

mysql

2 个回答

  1. Best Answer
    SELECT b.*, (SELECT MAX(s.`time`) 
             FROM s  
             WHERE s.`b_id` = b.`id`
            ) AS max_s_time
FROM b
    • 5

  2. 使用以下表达式：

    SELECT 
  b.id,
  MAX(s.time) AS time
FROM
  b 
  LEFT JOIN s 
    ON b.id = s.b_id 
GROUP BY b.id
    • 0

相关问题