我有一张文件表(此处为简化版):
id | rev | content |
---|---|---|
1 | 1 | ... |
2 | 1 | ... |
1 | 2 | ... |
1 | 3 | ... |
如何为每个id选择一行,并且仅选择最大的rev?对于上面的数据,结果应该包含两行:[1,3,…]和[2,1,…]。我使用的是MySQL。
目前,我在while循环中使用检查来检测和重写结果集中的旧版本。但这是实现结果的唯一方法吗?没有SQL解决方案吗?
我有一张文件表(此处为简化版):
id | rev | content |
---|---|---|
1 | 1 | ... |
2 | 1 | ... |
1 | 2 | ... |
1 | 3 | ... |
如何为每个id选择一行,并且仅选择最大的rev?对于上面的数据,结果应该包含两行:[1,3,…]和[2,1,…]。我使用的是MySQL。
目前,我在while循环中使用检查来检测和重写结果集中的旧版本。但这是实现结果的唯一方法吗?没有SQL解决方案吗?
当前回答
当您将rev和id组合为MAX()的一个maxRevId值,然后将其拆分为原始值时,可以在不进行联接的情况下进行选择:
SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
FROM YourTable
GROUP BY id) x;
当存在复杂联接而不是单个表时,这一速度特别快。使用传统方法,复杂连接将进行两次。
当rev和id为INT UNSIGNED(32位)并且组合值适合BIGINT UNSIGNED时(64位),上述组合对于位函数很简单。当id和rev大于32位值或由多个列组成时,需要将该值合并为二进制值,例如,为MAX()添加适当的填充。
其他回答
我不能保证它的性能,但这里有一个受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的值,例如
3.2变为1003.20157变为1057.001923.88变为1923.881
如果你做得对,两个数字的字符串比较应该产生与两个数字数字比较相同的“最大值”,并且很容易使用substring函数转换回原始数字(这在任何地方都可以使用一种或另一种形式)。
我惊讶地发现,没有答案提供SQL窗口函数解决方案:
SELECT a.id, a.rev, a.contents
FROM (SELECT id, rev, contents,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) ranked_order
FROM YourTable) a
WHERE a.ranked_order = 1
在SQL标准ANSI/ISO标准SQL:2003中添加了窗口(或窗口)功能,后来又用ANSI/ISO标准SQL:2008进行了扩展。有更多类型的秩函数可用于处理平局问题:rank、DENSE_rank、PERSENT_rank。
按相反顺序对rev字段进行排序,然后按id进行分组,每个分组的第一行是rev值最高的一行。
SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;
测试时间:http://sqlfiddle.com/具有以下数据
CREATE TABLE table1
(`id` int, `rev` int, `content` varchar(11));
INSERT INTO table1
(`id`, `rev`, `content`)
VALUES
(1, 1, 'One-One'),
(1, 2, 'One-Two'),
(2, 1, 'Two-One'),
(2, 2, 'Two-Two'),
(3, 2, 'Three-Two'),
(3, 1, 'Three-One'),
(3, 3, 'Three-Three')
;
这在MySql 5.5和5.6中给出了以下结果
id rev content
1 2 One-Two
2 2 Two-Two
3 3 Three-Two
乍一看。。。
您只需要一个带有MAX聚合函数的GROUP BY子句:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
从来没有这么简单,是吗?
我刚刚注意到你也需要内容栏。
这是SQL中一个非常常见的问题:在每个组标识符的列中查找具有某个最大值的行的整个数据。在我的职业生涯中我听过很多。事实上,这是我在当前工作的技术面试中回答的问题之一。
事实上,StackOverflow社区创建了一个标签来处理这样的问题是非常常见的:每个组中最优秀的一个。
基本上,有两种方法可以解决这个问题:
使用简单组标识符连接,组子查询中的最大值
在这种方法中,首先在子查询中找到组标识符,即组中的最大值(上面已经解决)。然后,将表连接到子查询,组标识符和组中的最大值相等:
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
左侧使用自身连接,调整连接条件和过滤器
在这种方法中,您可以将表与自身连接起来。组标识符中包含相等项。然后,两个聪明的动作:
第二个连接条件的左侧值小于右侧值执行步骤1时,实际具有最大值的行的右侧将为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;
结论
这两种方法带来的结果完全相同。
如果组标识符的组中有两行具有最大值,则两种方法中的结果都是这两行。
这两种方法都与SQLANSI兼容,因此,无论其“味道”如何,都可以与您喜爱的RDBMS一起使用。
这两种方法都是性能友好的,但是您的里程可能会有所不同(RDBMS、DB结构、索引等)。确保你选择对你最有意义的那一个。
这些答案对我来说都不管用。
这就是对我有用的。
with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max