获取组内最新记录 [英] get latest record in group

问题描述

我有以下表结构

id    dateChanged    changeType    changeID

有以下数据

1   2014-04-01  memEdit   205
2   2014-04-01  memEdit   205
3   2014-03-31  memEdit   1
4   2014-04-01  memEdit   1
5   2014-04-01  memEdit   3

现在我想获取每个更改 ID 的最新记录.所以我会得到 ID 为 2、4、5 的记录.我试过将它们分组，但它给了我每组中的第一个(所以我得到了 1、3、5)

Now i want to get the latest record for each change id. So i'd get the records with ids 2,4,5. I've tried grouping them, but its giving me the first of each set (so i'm getting 1,3,5)

推荐答案

1.旧不工作查询

<打击>

SELECT * FROM `your_table`
GROUP BY `changeID`
ORDER BY `id` DESC

问题在于 MySQL 的非标准疯狂.

The problem is with MySQL's non-standards craziness.

MySQL 不会强制您GROUP BY 包含在SELECT 列表中的每一列.因此，如果您只 GROUP BY 一列但总共返回 4 列，则不能保证其他列值将属于分组的 changeID 记录，即回.如果该列不在 GROUP BY 中，MySQL 将选择应返回的值.

MySQL does not force you to GROUP BY every column that you include in the SELECT list. As a result, if you only GROUP BY one column but return 4 columns in total, there is no guarantee that the other column values will belong to the grouped changeID record that is returned. If the column is not in a GROUP BY MySQL chooses what value should be returned.

使用子查询将保证每次都返回正确的记录值.

Using the subquery will guarantee that the correct record values are returned every time.

正确且工作查询是:

SELECT `t`.* FROM(
    SELECT * FROM `smdbs_app`
    ORDER BY `id` DESC
) `t`
GROUP BY `changeID`
ORDER BY `id`

这篇关于获取组内最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！