从两个表和最新条目中选择

I keep running on an issue in calling from MySQL and getting what I need. I have two tables called projects and entries, what I am trying to do is get the latest time stamped entry. The SQL query is as follows:

我一直在运行从MySQL调用并获得我需要的问题。我有两个名为项目和条目的表,我想要做的是获取最新的时间戳条目。 SQL查询如下:

SELECT 
    projects.ProjectLogo, projects.ProjectLink, projects.ProjectDescription, 
    entries.EntryNum, entries.Votes, entries.Views, entries.Update 
FROM 
    projects 
    LEFT JOIN entries 
        ON projects.ProjectID = entries.ProjectID 
        AND projects.Media = 'image' 
        AND projects.Type = 'fan-art' 
GROUP BY 
    projects.ProjectID 
ORDER BY 
    entries.Update DESC

The issue is that I get the results but not the latest entry, I have used MAX(entries.Update) but it does not work. Any suggestions? Why does it not work?

问题是我得到的结果,但不是最新的条目,我使用MAX(entries.Update),但它不起作用。有什么建议?为什么不起作用?

3 个解决方案

#1

You can use a subquery to get the latest Update for every ProjectID on table entries. The result of the subquery is then join back on the two join statements providied that it will match on two columns: ProjectID and Update.

您可以使用子查询来获取表条目上每个ProjectID的最新更新。然后,子查询的结果将连接回两个连接语句,提示它将匹配两列:ProjectID和Update。

SELECT  projects.ProjectLogo, 
        projects.ProjectLink, 
        projects.ProjectDescription, 
        entries.EntryNum, 
        entries.Votes, 
        entries.Views, 
        entries.Update 
FROM    projects 
        INNER JOIN entries 
            ON  projects.ProjectID = entries.ProjectID 
        INNER JOIN  
        (
            SELECT  a.ProjectID, MAX(a.Update) max_val
            FROM    entries a
            GROUP   BY a.ProjectID
        ) b ON  b.ProjectID = entries.ProjectID AND
                b.max_val = entries.Update
WHERE   projects.Media = 'image' AND 
        projects.Type = 'fan-art' 
ORDER   BY entries.Update DESC

#2

Try using a subquery to get the latest entry:

尝试使用子查询来获取最新条目:

select projectId, max(`update`) as lastUpdate
from entries
group by projectId

And now, use this subquery to get what you need:

现在,使用此子查询来获得所需内容:

select ...
from 
    projects as p
    inner join entries as e on p.projectId=e.projectId
    inner join (
        select projectId, max(`update`) as lastUpdate
        from entries
        group by projectId) as me on e.`update`=me.lastUpdate

Hope this helps you

希望这对你有所帮助

#3

You have to restrict the join to only the record (row) with the latest entry date. if the pk in entries is chronologically increasing, you can use it.

您必须将连接限制为仅具有最新输入日期的记录(行)。如果条目中的pk按时间顺序增加,则可以使用它。

Select p.ProjectLogo, p.ProjectLink, p.ProjectDescription, 
      e.EntryNum, e.Votes, e.Views, e.Update 
From projects p 
   Left Join entries e 
     On e.EntryId =
        (Select(Max(entryId) from entries
         where ProjectID = p.ProjectID)
Where p.Media = 'image' 
    And p.Type = 'fan-art' 
Group By p.ProjectID 
Order By e.Update Desc

Otherwise, you need a double nested subquery

否则,您需要一个双嵌套子查询

Select p.ProjectLogo, p.ProjectLink, p.ProjectDescription, 
      e.EntryNum, e.Votes, e.Views, e.Update 
From projects p 
   Left Join entries e 
     On e.EntryId =
        (Select entryId from entries
         where ProjectID = p.ProjectID
            And update =
                (Select max(update) From entries
                 Where ProjectID = p.ProjectID))                        
where p.Media = 'image' 
    And p.Type = 'fan-art' 
Group By p.ProjectID 
Order By e.Update Desc

#1