MySQL 根据接受的条件查找最近更新的项目

Question

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

• ID
• id_number
• 描述
• 注册日期
• 已接受

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

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

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

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

非常感谢您抽出时间。

Answer 1

尝试这个查询:

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 的查询运行得更快.

它是如何工作的

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

您的请求是为每个 id_number 选择具有最新(最大)的行 register_date ，具有 accepted = 1 的行优先于 accepted = 0 的人。这意味着，对于 id_number 的每个值，选择具有该对最大值 (accepted, register_date) 的行按字典顺序排列。

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

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

返回查询，该对的条件(accepted, register_date)的l小于同一对n是连接条件的一部分。

因为LEFT JOIN ，当一行 l (左表)在 n 中没有匹配项(右表)，一行全是NULL使用 s 代替(对于 n 的列)。

连接条件匹配 l 中的所有行行来自 n具有相同的 id_number并且在 (accepted, register_date) 上更大部分。 l行最大值为 (accepted, register_date) n 中不会有任何匹配项，正在完成 NULL s。

WHERE子句仅保留具有 NULL 的行在选自 n 的列中，这意味着它们在 (accepted, register_date) 上具有最大值一对。

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

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