我有此表用于文档(此处为简化版):
+------+-------+--------------------------------------+
| id | rev | content |
+------+-------+--------------------------------------+
| 1 | 1 | ... |
| 2 | 1 | ... |
| 1 | 2 | ... |
| 1 | 3 | ... |
+------+-------+--------------------------------------+
如何为每个 ID 选择一行,而仅选择最大转速?
根据上述数据,结果应包含两行: [1, 3, ...]
和[2, 1, ..]
。我正在使用MySQL 。
目前,我在while
循环中使用检查功能来检测并覆盖结果集中的旧版本。但这是获得结果的唯一方法吗?没有SQL解决方案吗?
更新资料
作为答案提示, 有一个 SQL 的解决方案,并且这里 sqlfiddle 演示 。
更新 2
在添加上述sqlfiddle 之后 ,我注意到问题被投票的速率已经超过答案的投票率。那不是意图!小提琴基于答案,尤其是已接受的答案。
您需要做的是带有MAX
聚合函数的GROUP BY
子句:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
我只是注意到您也需要content
列。
在 SQL 中,这是一个非常常见的问题:查找行的整个数据,并在每个组标识符的列中找到某个最大值。我在职业生涯中听到了很多。实际上,这是我在当前工作的技术面试中回答的问题之一。
实际上,是如此普遍,以至于 StackOverflow 社区创建了一个标签来处理这样的问题: great-n-per-group 。
基本上,有两种方法可以解决该问题:
group-identifier, max-value-in-group
子查询结合在这种方法中,您首先要在子查询中找到group-identifier, max-value-in-group
即group-identifier, max-value-in-group
(已在上面解决)。然后,在group-identifier
和max-value-in-group
相等的情况下,将表连接到子查询:
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
SELECT id, MAX(rev) rev
FROM YourTable
GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev
在这种方法中,您无需将表本身连接起来。平等在group-identifier
。然后,有 2 个明智的举动:
NULL
(这是LEFT JOIN
,还记得吗?)。然后,我们过滤联接的结果,仅显示右侧为NULL
的行。 因此,您最终得到:
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;
两种方法都带来完全相同的结果。
如果您有两行具有用于group-identifier
max-value-in-group
,则两种方法的结果都将出现在这两行中。
两种方法都与 SQL ANSI 兼容,因此,无论其 “味道” 如何,都将与您喜欢的 RDBMS 一起使用。
两种方法都对性能很友好,但是您的工作量可能会有所不同(RDBMS,数据库结构,索引等)。因此,当您选择一种方法而不是另一种方法时,请选择基准 。并确保选择最有意义的一种。
我的偏好是使用尽可能少的代码...
您可以使用IN
尝试以下操作:
SELECT *
FROM t1 WHERE (id,rev) IN
( SELECT id, MAX(rev)
FROM t1
GROUP BY id
)
在我看来,它并不那么复杂... 更易于阅读和维护。
我很惊讶没有答案提供 SQL 窗口函数解决方案:
SELECT a.id, a.rev, a.contents
FROM (SELECT id, rev, contents,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
FROM YourTable) a
WHERE a.rank = 1
在 SQL 标准 ANSI / ISO 标准 SQL:2003 中添加,并在以后通过 ANSI / ISO 标准 SQL:2008 进行了扩展,窗口(或窗口)功能现在可用于所有主要供应商。有更多类型的排名函数可用于处理平局问题: RANK, DENSE_RANK, PERSENT_RANK
。
另一个解决方案是使用相关子查询:
select yt.id, yt.rev, yt.contents
from YourTable yt
where rev =
(select max(rev) from YourTable st where yt.id=st.id)
在(id,rev)上有一个索引几乎使子查询成为一个简单的查询...
以下是对 @AdrianCarneiro 的答案(子查询,leftjoin)中的解决方案的比较,该解决方案基于 MySQL 的测量结果,其中 InnoDB 表具有约 100 万条记录,组大小为 1-3。
虽然对于全表扫描,子查询 / leftjoin / 相关时间相互关联为 6/8/9,但在直接查找或批处理( id in (1,2,3)
)时,子查询要比其他查询慢得多(由于重新运行子查询)。但是,我无法区分左连接和相关解决方案的速度。
最后一点,当 leftjoin 在组中创建 n *(n + 1)/ 2 个连接时,其性能可能会受到组大小的严重影响。
我不能保证性能,但这是受 Microsoft Excel 局限性启发的技巧。它有一些好的功能
好东西
方法
这有点丑陋,要求您对rev列的有效值范围有所了解。让我们假设我们知道rev列是介于 0.00 和 999 之间的数字,包括小数,但是小数点右边永远只有两位数字(例如 34.17 是有效值)。
要点是,您可以通过字符串将主要比较字段与所需数据串联 / 打包来创建单个合成列。这样,您可以强制 SQL 的 MAX()聚合函数返回所有数据(因为它已打包到单个列中)。然后,您必须解压缩数据。
这是上面用 SQL 编写的示例的外观
SELECT id,
CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev
FROM (SELECT id,
CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
FROM yourtable
)
GROUP BY id
打包首先通过将rev列强制为多个已知字符长度来实现,而不管rev的值如何,例如
如果操作正确,则两个数字的字符串比较应产生与两个数字的数字比较相同的 “最大值”,并且使用 substring 函数(以一种形式或另一种形式可用)很容易转换回原始数字到处)。
我认为这是最简单的解决方案:
SELECT *
FROM
(SELECT *
FROM Employee
ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
SELECT *
:返回所有字段。 FROM Employee
:已搜索表格。 (SELECT *...)
子查询:返回所有人员,按薪水排序。 GROUP BY employeesub.Salary
:强制将每位员工的排在最前的 Salary 行作为返回结果。 如果您碰巧只需要一行,那就更简单了:
SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1
我还认为,分解,理解和修改其他目的是最容易的:
ORDER BY Employee.Salary DESC
:按薪水ORDER BY Employee.Salary DESC
结果,薪水最高。 LIMIT 1
:仅返回一个结果。 理解这种方法,解决这些类似问题中的任何一个都变得微不足道:获得薪水最低的员工(将DESC
更改为ASC
),获得收入最高的十个员工(将LIMIT 1
更改为LIMIT 10
),通过另一个字段进行排序(更改ORDER BY Employee.Salary
到ORDER BY Employee.Commission
等)。
像这样吗
SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
SELECT id, max(rev) as maxrev FROM yourtable
WHERE yourtable
GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)
完成这项工作的另一种方法是在 OVER PARTITION 子句中使用MAX()
分析函数
SELECT t.*
FROM
(
SELECT id
,rev
,contents
,MAX(rev) OVER (PARTITION BY id) as max_rev
FROM YourTable
) t
WHERE t.rev = t.max_rev
这篇文章中已经记录的其他ROW_NUMBER()
OVER PARTITION 解决方案是
SELECT t.*
FROM
(
SELECT id
,rev
,contents
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
FROM YourTable
) t
WHERE t.rank = 1
此 2 SELECT 在 Oracle 10g 上运行良好。
MAX()解决方案肯定比ROW_NUMBER()
解决方案运行得更快,因为MAX()
复杂度为O(n)
而ROW_NUMBER()
复杂度最小为O(n.log(n))
,其中n
表示表中的记录数!
我喜欢针对此问题使用基于NOT EXIST
的解决方案:
SELECT
id,
rev
-- you can select other columns here
FROM YourTable t
WHERE NOT EXISTS (
SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)
这将选择组中具有最大值的所有记录,并允许您选择其他列。