MySQL - 从与第一个表匹配的第二个表中选择最后一条记录

MySQL - Select last record from second table matching with first table

提问by Alyas

I have two tables customers and orders, below is the structure.

我有两个表customers和orders，下面是结构。

Table - customers

表 - 客户

• id
• customer_name

• ID
• 顾客姓名

Table - orders

表 - 订单

• id
• order_id
• customer_id

• ID
• 订单编号
• 顾客ID

customers table have customers records and orders table have orders placed by customers,

客户表有客户记录，订单表有客户下的订单，

customer_idin orders table is linked to the idfield of customers table.

订单表中的customer_id链接到客户表的id字段。

Now one customer can have zero or one or more than one orders, i want to get the last order placed by customers only.

现在一个客户可以有零个或一个或多个订单，我只想得到客户下的最后一个订单。

when i run the following query a simple invisible join, it returns all the orders by the customer

当我运行以下查询一个简单的隐形连接时，它返回客户的所有订单

SELECT customers.customer_name,orders.order_id FROM orders,customers WHERE orders.customer_id=customers.id

I have also tried different JOIN statements but cannot get the last order by the customer, i want to get it in one SQL query for all customers.

我也尝试了不同的 JOIN 语句，但无法获得客户的最后一个订单，我想在所有客户的一个 SQL 查询中获得它。

Thank you in advance for your help.

预先感谢您的帮助。

采纳答案by Meherzad

Try this query

试试这个查询

SELECT 
   c.customer_name, 
   max(o.order_id)
FROM 
   customers c
INNER JOIN
   orders o
ON
   o.customer_id = c.id
GROUP BY 
   c.customer_name

You don't have any date field in the order table so assuming the latest order will be the one which has max(order_id).

订单表中没有任何日期字段，因此假设最新的订单将是具有max(order_id).

回答by vinibarr

In MySQL there is just few ways to make it work (that I now actually). The first one is sort your table as descbefore the join:

在 MySQL 中，只有几种方法可以使它工作（我现在实际上是这样）。第一个是像desc之前一样对您的表格进行排序join：

SELECT c.customer_name, o.customer_id, o.order_id,o.id FROM customers c 
INNER JOIN orders o 
    ON o.id = (SELECT id FROM orders WHERE customer_id = c.id ORDER BY id DESC LIMIT 1)

Using in real time is the only way to get it done, but if you need to make some join on not real time you can create a temporary table or a alias table sorting it to make your select, like this:

实时使用是完成它的唯一方法，但是如果您需要进行一些非实时连接，您可以创建一个临时表或别名表对其进行排序以进行选择，如下所示：

CREATE TABLE tmp_your_table AS 
SELECT * FROM orders ORDER BY id DESC

So now you are able to make this join work:

所以现在你可以让这个连接工作：

SELECT c.customer_name, o.customer_id, o.order_id,o.id FROM customers c 
INNER JOIN tmp_your_table o ON o.id = tmp_your_table.id

回答by Jeeva

Try this query

试试这个查询

SELECT 
   c.customer_name, 
   o.order_id
FROM 
   customers c
INNER JOIN
   orders o
ON
   o.customer_id = c.id
ORDER BY 
   o.id desc
LIMIT 1;