mysql - 对有序子查询中的第一个结果进行分组

Question

在 mysql 中，我无法根据最大的值为每个foreign_id提取一行。奇怪的是，不同版本的mysql都可以工作(如下所列)

id  foreign_id  value
---------------------
1   1           1000
2   1           2000
3   2           2000
4   2           1000
5   3           2000

我尝试提取 id 2,3,5 而不是 1,3,5

CREATE TABLE `docs` (
  `id` int(11) NOT NULL,
  `foreign_id` int(6) DEFAULT NULL,
  `value` int(8) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `docs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `foreign_id_index` (`foreign_id`);

INSERT INTO `docs` (`id`, `foreign_id`, `value`) VALUES
(1, 1, 1000), (2, 1, 2000), (3, 2, 2000), (4, 2, 1000), (5, 3, 2000)


select 
  docs.id, docs.foreign_id, docs.value 
FROM docs
INNER JOIN
  (select id, max(value) from docs group by foreign_id) sub
  ON sub.id = docs.id
# expected results are ids (2,3,5), not (1,3,5)

Answer 1

它比看起来更简单

SELECT D.id, D.foreign_id, max_vals.max_val as value
FROM docs D
JOIN
   (SELECT foreign_id, MAX(value) as max_val
    FROM docs
    GROUP BY foreign_id) max_vals
ON D.foreign_id=max_vals.foreign_id and D.value=max_vals.max_val

在这种情况下，您需要 JOIN 而不是 INNER JOIN

有一个困难的情况，即有 2 个或更多 foreign_id 具有相同的 MAX value...