MySQL查询-仅使用最新版本的条目进行内部联接 [英] MySQL query - Inner join using only the latest version of an entry

问题描述

我有一个名为jobs的表，其中包含各种信息.每个工作都有一个工作编号(唯一ID).

I have a table, named jobs with various pieces of information. Each job is given a job number (unique id).

然后有另一个名为purchaseOrders的表，它具有jobID的FK和poID的PK.

There is then another table, named purchaseOrders that has a FK of jobID and a PK of poID.

在编辑采购订单条目时，将保存旧信息...意味着，我创建了一个新的PO条目(新的唯一ID).

when a purchase order entry is edited, the old information is saved... meaning, i create a new PO entry (new unique id).

我要做的是编写一个查询，该查询选择作业"中的所有字段，并选择"purchaseOrders"中的所有字段，但仅选择该作业的最新poID.

What i'm trying to do, is write one query that selects all fields from "jobs" and all fields from "purchaseOrders" but only the latest poID for that job.

例如:

jobID      Name      State            poID      time      jobID
==========================            ==========================
1          foo       fl               1         1:00      1
2          bar       ga               2         1:10      1
3          zzz       ny               3         1:20      1
                                      4         2:00      2
                                      5         2:01      2
                                      6         2:30      2
                                      7         3:00      3
                                      8         3:40      3
                                      9         3:15      3

如何运行一个查询，该查询将从两个表中选择所有列，但只包含特定jobID具有最高poID的信息?

How can I run a query that will select all the columns from both tables, but only include the information with the highest poID for the specific jobID?

推荐答案

SELECT  a.*, c.*
FROM    jobs a
        INNER JOIN
        (
            SELECT jobID, MAX(time) maxVal
            FROM purchaseOrders
            GROUP BY jobID
        ) b ON a.jobID = b.jobID
        INNER JOIN purchaseOrders c
            ON c.jobID = b.JobID AND
                c.time = b.maxVal

• SQLFiddle演示
• SQLFiddle Demo

这篇关于MySQL查询-仅使用最新版本的条目进行内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！