从左联接中选择最大的一行 [英] SELECT biggest row from a LEFT JOIN

问题描述

我有一个SQL查询，该查询将表左联接到其中.这将导致主表中的行被复制，但JOINed表中的行不同.如何仅从JOINed表中选择日期最高的行.

I have a SQL query that LEFT JOINs a table into it. This causes rows from the main table to be duplicated but with different rows from the JOINed table. How do I only select the rows with the highest date from the JOINed table.

这是一个例子(这是我查询的结果):

Here's an example (this is the result from my query):

ID    Message    Date
---------------------------
0     Hi         2011-01-01
0     Bye        2011-02-05
0     Hello      2011-04-20
1     Test       2010-12-31
1     Testing    2010-11-15
2     Something  2010-12-12
2     Nothing    2011-01-01
2     Yes        2010-02-05
3     Cool       NULL

我想要每个ID一行，ID最高的那一行.

I want one row per ID, the row with the highest ID.

ID    Message    Date
---------------------------
0     Hello      2011-04-20
1     Test       2010-12-31
2     Nothing    2011-01-01
3     Cool       NULL

我当前的查询是这样的(我只是弥补了这一点，但它类似于真实的查询):

My current query is something like this (I just kinda made this up, but it's similar to the real one):

SELECT t1.ID, t2.Message, t2.Date
FROM t1
LEFT JOIN (
  SELECT t3.ID, t3.message, t3.Date
  FROM t3
  LEFT JOIN t4 ON t4.userID = 12 AND t3.ID = t4.ID
  WHERE t4.color = 'blue'
) AS t2
ON t1.ID = t2.ID
WHERE t1.userID = 12

我想我可以使用PHP并遍历结果并挑选出想要的结果，但是我可以让MySQL帮我吗?

I guess I could use PHP and loop through the results and pick out the ones I want, but can I have MySQL do that for me?

编辑:抱歉，我的第一个例子是 way 错误，这更像是我想要的.

EDIT: Sorry, my 1st example was way wrong, this is more like what I want.

编辑2 :我尝试使用GROUP BY和MAX，但是我认为我做错了.

EDIT 2: I tried using GROUP BY and MAX, but I think I'm doing something wrong.

我尝试过:

SELECT t1.ID, t2.Message, MAX(t2.Date)
FROM t1
LEFT JOIN (
  SELECT t3.ID, t3.message, t3.Date
  FROM t3
  LEFT JOIN t4 ON t4.userID = 12 AND t3.ID = t4.ID
  WHERE t4.color = 'blue'
) AS t2
ON t1.ID = t2.ID
WHERE t1.userID = 12
GROUP BY t1.ID

但是，这给了我

ID    Message    Date
---------------------------
0     Hi         2011-04-20
1     Test       2010-12-31
2     Something  2011-01-01
3     Cool       NULL

如何获取与最高日期关联的消息.

How do I get the Message associated with the highest date.

推荐答案

像这样:

SELECT 
   t1.ID, 
   t1.Message, 
   MAX(t2.Date) as [Date]
FROM t1
  LEFT JOIN (
    SELECT t3.ID, t3.Date
    FROM t3
    LEFT JOIN t4 ON t4.userID = 12 AND t3.ID = t4.ID
    WHERE t3.color = 'blue'
 ) AS t2
 ON t1.ID = t2.ID
 WHERE t1.userID = 12
 GROUP BY t1.ID, t1.Message

您可以使用GROUP BY对某些值进行分组，但必须限制对选择列表中的所有值进行分组，除非它是 aggregate函数 >，例如MAX.

You can use GROUP BY to group on certain values with the restriction that you have to group on all the values in the select list, unless it is an aggregate function, like MAX.

这篇关于从左联接中选择最大的一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！