MySQL 仅加入最近的一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3619030/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL JOIN the most recent row only?
提问by bcmcfc
I have a table customer that stores a customer_id, email and reference. There is an additional table customer_data that stores a historical record of the changes made to the customer, i.e. when there's a change made a new row is inserted.
我有一个表 customer 存储 customer_id、电子邮件和参考。还有一个额外的表 customer_data 存储对客户所做更改的历史记录，即当发生更改时插入新行。
In order to display the customer information in a table, the two tables need to be joined, however only the most recent row from customer_data should be joined to the customer table.
为了在一个表中显示客户信息，需要将两个表连接起来，但是只有 customer_data 中的最新行才应该连接到客户表中。
It gets a little more complicated in that the query is paginated, so has a limit and an offset.
它变得有点复杂，因为查询是分页的，所以有一个限制和一个偏移量。
How can I do this with MySQL? I think I'm wanting to put a DISTINCT in there somewhere...
我怎样才能用 MySQL 做到这一点？我想我想在某个地方放一个 DISTINCT ......
The query at the minute is like this-
此刻的查询是这样的——
SELECT *, CONCAT(title,' ',forename,' ',surname) AS name
FROM customer c
INNER JOIN customer_data d on c.customer_id=d.customer_id
WHERE name LIKE '%Smith%' LIMIT 10, 20
Additionaly, am I right in thinking I can use CONCAT with LIKE in this way?
另外，我认为我可以以这种方式将 CONCAT 与 LIKE 一起使用是否正确？
(I appreciate that INNER JOIN might be the wrong type of JOIN to use. I actually have no clue what the difference is between the different JOINs. I'm going to look into that now!)
（我很欣赏 INNER JOIN 可能是错误的 JOIN 类型。我实际上不知道不同 JOIN 之间的区别是什么。我现在要研究一下！）
回答by Daniel Vassallo
You may want to try the following:
您可能想尝试以下操作：
SELECT CONCAT(title, ' ', forename, ' ', surname) AS name
FROM customer c
JOIN (
SELECT MAX(id) max_id, customer_id
FROM customer_data
GROUP BY customer_id
) c_max ON (c_max.customer_id = c.customer_id)
JOIN customer_data cd ON (cd.id = c_max.max_id)
WHERE CONCAT(title, ' ', forename, ' ', surname) LIKE '%Smith%'
LIMIT 10, 20;
Note that a JOINis just a synonym for INNER JOIN.
请注意， aJOIN只是 的同义词INNER JOIN。
Test case:
测试用例：
CREATE TABLE customer (customer_id int);
CREATE TABLE customer_data (
id int,
customer_id int,
title varchar(10),
forename varchar(10),
surname varchar(10)
);
INSERT INTO customer VALUES (1);
INSERT INTO customer VALUES (2);
INSERT INTO customer VALUES (3);
INSERT INTO customer_data VALUES (1, 1, 'Mr', 'Bobby', 'Smith');
INSERT INTO customer_data VALUES (2, 1, 'Mr', 'Bob', 'Smith');
INSERT INTO customer_data VALUES (3, 2, 'Mr', 'Jane', 'Green');
INSERT INTO customer_data VALUES (4, 2, 'Miss', 'Jane', 'Green');
INSERT INTO customer_data VALUES (5, 3, 'Dr', 'Hyman', 'Black');
Result (query without the LIMITand WHERE):
结果（不带LIMIT和的查询WHERE）：
SELECT CONCAT(title, ' ', forename, ' ', surname) AS name
FROM customer c
JOIN (
SELECT MAX(id) max_id, customer_id
FROM customer_data
GROUP BY customer_id
) c_max ON (c_max.customer_id = c.customer_id)
JOIN customer_data cd ON (cd.id = c_max.max_id);
+-----------------+
| name |
+-----------------+
| Mr Bob Smith |
| Miss Jane Green |
| Dr Hyman Black |
+-----------------+
3 rows in set (0.00 sec)
回答by Danny Coulombe
If you are working with heavy queries, you better move the request for the latest row in the where clause. It is a lot faster and looks cleaner.
如果您正在处理大量查询，则最好在 where 子句中移动对最新行的请求。它更快，看起来更干净。
SELECT c.*,
FROM client AS c
LEFT JOIN client_calling_history AS cch ON cch.client_id = c.client_id
WHERE
cch.cchid = (
SELECT MAX(cchid)
FROM client_calling_history
WHERE client_id = c.client_id AND cal_event_id = c.cal_event_id
)
回答by Thomas
Presuming the autoincrement column in customer_datais named Id, you can do:
假设自动增量列customer_data名为 named Id，您可以执行以下操作：
SELECT CONCAT(title,' ',forename,' ',surname) AS name *
FROM customer c
INNER JOIN customer_data d
ON c.customer_id=d.customer_id
WHERE name LIKE '%Smith%'
AND d.ID = (
Select Max(D2.Id)
From customer_data As D2
Where D2.customer_id = D.customer_id
)
LIMIT 10, 20
回答by payne8
For anyone who must work with an older version of MySQL (pre-5.0 ish) you are unable to do sub-queries for this type of query. Here is the solution I was able to do and it seemed to work great.
对于必须使用旧版本 MySQL（5.0 之前）的任何人，您无法为此类查询执行子查询。这是我能够做的解决方案，它似乎工作得很好。
SELECT MAX(d.id), d2.*, CONCAT(title,' ',forename,' ',surname) AS name
FROM customer AS c
LEFT JOIN customer_data as d ON c.customer_id=d.customer_id
LEFT JOIN customer_data as d2 ON d.id=d2.id
WHERE CONCAT(title, ' ', forename, ' ', surname) LIKE '%Smith%'
GROUP BY c.customer_id LIMIT 10, 20;
Essentially this is finding the max id of your data table joining it to the customer then joining the data table to the max id found. The reason for this is because selecting the max of a group doesn't guarantee that the rest of the data matches with the id unless you join it back onto itself.
本质上，这是找到您的数据表的最大 id 将其连接到客户，然后将数据表连接到找到的最大 id。这样做的原因是因为选择组的最大值并不能保证其余数据与 id 匹配，除非您将其重新连接到自身上。
I haven't tested this on newer versions of MySQL but it works on 4.0.30.
我没有在较新版本的 MySQL 上测试过这个，但它适用于 4.0.30。
回答by Benjamin
I know this question is old, but it's got a lot of attention over the years and I think it's missing a concept which may help someone in a similar case. I'm adding it here for completeness sake.
我知道这个问题很老，但多年来它得到了很多关注，我认为它缺少一个可能在类似情况下帮助某人的概念。为了完整起见，我在这里添加它。
If you cannot modify your original database schema, then a lot of good answers have been provided and solve the problem just fine.
如果您无法修改原始数据库架构，那么已经提供了很多很好的答案并且可以很好地解决问题。
If you can, however, modify your schema, I would advise to add a field in your customertable that holds the idof the latest customer_datarecord for this customer:
但是，如果您可以修改您的架构，我建议在您的customer表中添加一个字段来保存该客户id的最新customer_data记录：
CREATE TABLE customer (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
current_data_id INT UNSIGNED NULL DEFAULT NULL
);
CREATE TABLE customer_data (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
customer_id INT UNSIGNED NOT NULL,
title VARCHAR(10) NOT NULL,
forename VARCHAR(10) NOT NULL,
surname VARCHAR(10) NOT NULL
);
Querying customers
查询客户
Querying is as easy and fast as it can be:
查询尽可能简单快捷：
SELECT c.*, d.title, d.forename, d.surname
FROM customer c
INNER JOIN customer_data d on d.id = c.current_data_id
WHERE ...;
The drawback is the extra complexity when creating or updating a customer.
缺点是创建或更新客户时的额外复杂性。
Updating a customer
更新客户
Whenever you want to update a customer, you insert a new record in the customer_datatable, and update the customerrecord.
每当您想要更新客户时，您都可以在customer_data表中插入一条新记录，然后更新该customer记录。
INSERT INTO customer_data (customer_id, title, forename, surname) VALUES(2, 'Mr', 'John', 'Smith');
UPDATE customer SET current_data_id = LAST_INSERT_ID() WHERE id = 2;
Creating a customer
创建客户
Creating a customer is just a matter of inserting the customerentry, then running the same statements:
创建客户只需插入customer条目，然后运行相同的语句：
INSERT INTO customer () VALUES ();
SET @customer_id = LAST_INSERT_ID();
INSERT INTO customer_data (customer_id, title, forename, surname) VALUES(@customer_id, 'Mr', 'John', 'Smith');
UPDATE customer SET current_data_id = LAST_INSERT_ID() WHERE id = @customer_id;
Wrapping up
包起来
The extra complexity for creating/updating a customer might be fearsome, but it can easily be automated with triggers.
创建/更新客户的额外复杂性可能令人恐惧，但可以通过触发器轻松实现自动化。
Finally, if you're using an ORM, this can be really easy to manage. The ORM can take care of inserting the values, updating the ids, and joining the two tables automatically for you.
最后，如果您使用的是 ORM，这真的很容易管理。ORM 可以为您处理插入值、更新 id 和连接两个表的工作。
Here is how your mutable Customermodel would look like:
以下是您的可变Customer模型的外观：
class Customer
{
private int id;
private CustomerData currentData;
public Customer(String title, String forename, String surname)
{
this.update(title, forename, surname);
}
public void update(String title, String forename, String surname)
{
this.currentData = new CustomerData(this, title, forename, surname);
}
public String getTitle()
{
return this.currentData.getTitle();
}
public String getForename()
{
return this.currentData.getForename();
}
public String getSurname()
{
return this.currentData.getSurname();
}
}
And your immutable CustomerDatamodel, that contains only getters:
而你的不可变CustomerData模型，只包含吸气剂：
class CustomerData
{
private int id;
private Customer customer;
private String title;
private String forename;
private String surname;
public CustomerData(Customer customer, String title, String forename, String surname)
{
this.customer = customer;
this.title = title;
this.forename = forename;
this.surname = surname;
}
public String getTitle()
{
return this.title;
}
public String getForename()
{
return this.forename;
}
public String getSurname()
{
return this.surname;
}
}
回答by Pramendra Gupta
SELECT CONCAT(title,' ',forename,' ',surname) AS name * FROM customer c
INNER JOIN customer_data d on c.id=d.customer_id WHERE name LIKE '%Smith%'
i think you need to change c.customer_id to c.id
我认为您需要将 c.customer_id 更改为 c.id
else update table structure
else 更新表结构
回答by Ajay Kumar
You can also do this
你也可以这样做
SELECT CONCAT(title, ' ', forename, ' ', surname) AS name
FROM customer c
LEFT JOIN (
SELECT * FROM customer_data ORDER BY id DESC
) customer_data ON (customer_data.customer_id = c.customer_id)
GROUP BY c.customer_id
WHERE CONCAT(title, ' ', forename, ' ', surname) LIKE '%Smith%'
LIMIT 10, 20;
回答by Bur?in
It's a good idea that logging actual data into "customer_data" table. With this data you can select all data from "customer_data" table as you wish.
将实际数据记录到“ customer_data”表中是个好主意。有了这些数据，您可以根据需要从“customer_data”表中选择所有数据。
