MySQL 根据接受的条件查找最近更新的项目 [英] MySQL find most recently updated item upon accepted condition

问题描述

我有一个包含以下列的表格:

I have a table with the following columns:

• id
• id_number
• 说明
• 注册日期
• 接受

id_number 不是唯一的，因为我可以在同一个表上有多个元素.我只需要根据以下条件找到每个 id_number 的一个元素:

The id_number is not unique since I can have multiple elements on the same table. I need to find just one element of each id_number with the following criteria:

• 如果该项目至少已被接受一次，我需要具有较新 register_date 的项目，但只查看已接受的同类项目.
• 如果该项目未被接受，那么我需要已注册的最新项目.

例如，如果我有以下数据:

For instance, if I have the following data:

ID Id_Number    Description             Register_Date         Accepted
==============================================================================
1  TA-001       This is a test           19/02/2015 15:40:35      0
2  TA-004       test                     27/03/2015 12:00:18      1
3  DCMIX-001    test                     01/01/2011 00:00:00      0
4  TA-005       test                     01/01/2010 00:00:00      0
5  TA-001       different description    25/03/2015 11:07:42      1
6  TA-001       not accepted but newer   25/03/2015 11:53:42      0
7  TA-005       test                     27/03/2015 13:10:21      0

将产生此输出:

ID Id_Number    Description             Register_Date         Accepted
==============================================================================   
2  TA-004       test                    27/03/2015 12:00:18       1
3  DCMIX-001    test                    01/01/2011 00:00:00       0    
5  TA-001       different description   25/03/2015 11:07:42       1
7  TA-005       test                    27/03/2015 13:10:21       0

非常感谢您抽出宝贵时间.

Many thanks for your time.

推荐答案

试试这个查询:

SELECT l.*
FROM mytable l                         # 'l' from 'last'
LEFT JOIN mytable n                    # 'n' from 'newer'
    ON l.id_number = n.id_number       # match by in_number
    AND (l.accepted < n.accepted OR    # 'l' < 'n' on (accepted, register_date)
         l.accepted = n.accepted AND l.register_date < n.register_date)
WHERE n.id_number IS NULL              # 'n' (newer) does not exist

它在单个查询中选择您需要的所有行，并且运行速度比使用 GROUP BY 的查询更快.

It selects all the rows you need in a single query and runs faster than the queries that use GROUP BY.

LEFT JOIN 将左表中的所有行(别名为 l 来自 last)与右表中的匹配行(别名为 n 来自 newer).表 l 中的每一行都与表 n 中具有相同 id_number 的所有行配对，并且还有一个条件 (accepted, register_date) 字段对.

The LEFT JOIN combines all the rows from the left table (aliased as l from last) with the matching rows from the right table (aliased as n from newer). Each row from table l is paired with all the rows from table n having the same id_number and there is also a condition of the (accepted, register_date) pair of fields.

您的请求是为每个 id_number 选择具有最新(最大)register_date 的行，具有 accepted = 1 的行优先于那些 accepted = 0.这意味着，对于 id_number 的每个值，选择具有 (accepted, register_date) 对的最大值的行进入字典顺序.

Your request is to select for each id_number the row having the most recent (the biggest) register_date, the rows having accepted = 1 being preferred to those with accepted = 0. This means, for each value of id_number, select the row that has the maximum value of the pair (accepted, register_date) into the lexicographical order.

就是这样，对于两对 (a, b) 和 (c, d):

That's it, for two pairs (a, b) and (c, d):

(a, b) < (c, d) if (a < c) or (a = c and b < d)

回到查询，l的(accepted, register_date)对小于同一对n的条件是加入条件的一部分.

Back to the query, the condition of the pair (accepted, register_date) of l being less than the same pair of n is part of the join condition.

由于LEFT JOIN，当一行l(左表)在n(右表)中没有匹配表)，而是使用充满 NULL 的行(对于 n 的列).

Because of the LEFT JOIN, when a row of l (the left table) does not have a match in n (the right table), a row full of NULLs is used instead (for the columns of n).

连接条件将 l 中的所有行与 n 中具有相同 id_number 且更大的行匹配 在 (accepted, register_date) 部分.(accepted, register_date)的最大值的l行在n中没有任何匹配，以NULL结束s.

The join condition matches all the rows from l with the rows from n that have the same id_number and are greater on the (accepted, register_date) part. The row of l having the biggest value of (accepted, register_date) will not have any match in n, being completed with NULLs.

WHERE 子句仅保留从 n 选择的列中具有 NULL 的行，这意味着它们具有 biggest<(accepted, register_date) 对上的/em> 值.

The WHERE clause keeps only the rows having NULL in the columns selected from n, which means they have the biggest value on the (accepted, register_date) pair.

最后SELECT子句从左表l中提取出你需要的列(n的列都是NULL 无论如何).

Finally, the SELECT clause extracts the columns you need from the left table l (the columns of n are all NULL anyway).

使用其他 WHERE 条件(如果需要)完成查询并添加适当的 ORDER BY 表达式以按所需顺序获取行.

Complete the query with other WHERE conditions (if you need them) and add the appropriate ORDER BY expressions to get the rows in the desired order.

这篇关于MySQL 根据接受的条件查找最近更新的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！