如何为所有返回的 mysql 查询分配日期值 [英] How to assign date value to all returned mysql queries

问题描述

假设我有一张这样的桌子:

Let's suppose I have a table as such:

ID  |  Date      |  Owner   |  Activity
----------------------------------------
 1  |  1/2/2011  |  Joe     |  Login
 2  |  2/1/2011  |  Mary    |  Logout
 1  |  2/3/2011  |  Bob     |  Process
 3  |  5/6/2010  |  Harry   |  Send
 2  |  8/1/2011  |  Alice   |  Hide

如何运行检索所有值的查询，但对于日期字段，我有每个条目的该 ID 的最大值(日期).

How can I run a query that retrieves all values but for the date field I have the max(date) for that id for EACH entry.

因此，如果我要选择 *(还有我还不知道如何运行的其他语句)，输出将是:

Thus if I were to select * (with other statements I don't yet know how to run) the output would be:

ID  |  Date      |  Owner   |  Activity
----------------------------------------
 1  |  2/3/2011  |  Joe     |  Login
 2  |  8/1/2011  |  Mary    |  Logout
 1  |  2/3/2011  |  Bob     |  Process
 3  |  5/6/2010  |  Harry   |  Send
 2  |  8/1/2011  |  Alice   |  Hide

推荐答案

按 ID 分组的子查询上的 INNER JOIN 返回 MAX(Date) 应该做的工作:

An INNER JOIN on a subquery grouping by ID to return the MAX(Date) should do the job:

SELECT 
  t.ID,
  d.maxdate
  t.Owner,
  t.Activity
FROM 
  tbl t INNER JOIN (
   SELECT ID, MAX(Date) AS maxdate FROM tbl GROUP BY ID
  ) d ON t.ID = d.ID

这篇关于如何为所有返回的 mysql 查询分配日期值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！