我有一张文件表(此处为简化版):
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解决方案吗?
当前回答
我想,你想要这个吗?
select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)
SQL Fiddle:在此处检查
其他回答
我不能保证它的性能,但这里有一个受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函数转换回原始数字(这在任何地方都可以使用一种或另一种形式)。
如果select语句中有多个字段,并且希望通过优化代码获得所有这些字段的最新值:
select * from
(select * from table_name
order by id,rev desc) temp
group by id
唯一标识符?对唯一标识符!
开发MySQL数据库的最佳方法之一是使用每个id AUTOINCREMENT(SourceMySQL.com)。这有很多优点,这里无法介绍。这个问题的问题是它的示例有重复的id。这忽视了唯一标识符的这些巨大优势,同时也让熟悉这一点的人感到困惑。
最清洁的解决方案
数据库Fiddle
较新版本的MySQL默认情况下启用了ONLY_FUL_GROUP_BY,这里的许多解决方案在测试中都会失败。
即使如此,我们也可以简单地选择DISTINCT someuniquefield、MAX(要选择的任何其他字段)、(*somethirdfield)等,而不必担心会了解结果或查询的工作方式:
SELECT DISTINCT t1.id, MAX(t1.rev), MAX(t2.content)
FROM Table1 AS t1
JOIN Table1 AS t2 ON t2.id = t1.id AND t2.rev = (
SELECT MAX(rev) FROM Table1 t3 WHERE t3.id = t1.id
)
GROUP BY t1.id;
SELECT DISTINCT Table1.id,max(Table1.rev),max(Table2.content):返回DISTINCT somefield,max()某个其他字段,最后一个max()是多余的,因为我知道它只是一行,但查询需要它。FROM雇员:在上搜索了表。JOIN Table1 AS Table2 ON Table2.rev=Table1.rev:在第一个表上加入第二个表,因为我们需要得到max(Table1.rev)的注释。GROUP BY Table1.id:强制将每个员工的排名靠前的薪资行作为返回结果。
请注意,由于OP的问题中“内容”是“…”,因此无法测试这是否有效。因此,我将其更改为“..a”,“..b”,因此,我们现在可以看到结果是正确的:
id max(Table1.rev) max(Table2.content)
1 3 ..d
2 1 ..b
为什么它是干净的?DISTINCT()、MAX()等都很好地利用了MySQL索引。这会更快。或者,如果您有索引,并将其与查看所有行的查询进行比较,则速度会快得多。
原始解决方案
在ONLY_FUL_GROUP_BY被禁用的情况下,我们仍然可以使用GROUP BY,但是我们只在Salary上使用它,而不在id上使用它:
SELECT *
FROM
(SELECT *
FROM Employee
ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
SELECT*:返回所有字段。FROM雇员:在上搜索了表。(SELECT*…)子查询:返回所有人员,按薪资排序。GROUP BY employeesub。Salary:强制将每个员工的顶部排序的Salary行作为返回结果。
唯一行解决方案
请注意关系数据库的定义:“表中的每一行都有自己的唯一键。”这意味着,在问题的示例中,id必须是唯一的,在这种情况下,我们可以这样做:
SELECT *
FROM Employee
WHERE Employee.id = 12345
ORDER BY Employee.Salary DESC
LIMIT 1
希望这是一个解决问题的解决方案,帮助每个人更好地了解数据库中发生的事情。
解释
这不是纯SQL。这将使用SQLAlchemy ORM。
我来这里寻求SQLAlchemy帮助,所以我将使用python/SQLAlchemi版本复制Adrian Carneiro的答案,特别是外部连接部分。
此查询回答了以下问题:
“你能把这组记录(基于相同id)中版本号最高的记录还给我吗”。
这允许我复制记录,更新它,增加它的版本号,并以一种可以显示随时间变化的方式复制旧版本。
Code
MyTableAlias = aliased(MyTable)
newest_records = appdb.session.query(MyTable).select_from(join(
MyTable,
MyTableAlias,
onclause=and_(
MyTable.id == MyTableAlias.id,
MyTable.version_int < MyTableAlias.version_int
),
isouter=True
)
).filter(
MyTableAlias.id == None,
).all()
在PostgreSQL数据库上测试。
我的偏好是使用尽可能少的代码。。。
您可以使用IN试试看:
SELECT *
FROM t1 WHERE (id,rev) IN
( SELECT id, MAX(rev)
FROM t1
GROUP BY id
)
在我看来,这不那么复杂。。。易于阅读和维护。