MySQL JOIN最近的行而已? [英] MySQL JOIN the most recent row only?
问题描述
我有一个表客户,其中存储有一个customer_id,电子邮件和参考.还有一个附加表customer_data,用于存储对客户所做的更改的历史记录,即当发生更改时,将插入新行.
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_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.
由于查询是分页的,因此变得有些复杂,因此有一个限制和一个偏移量.
It gets a little more complicated in that the query is paginated, so has a limit and an offset.
如何使用MySQL做到这一点?我想我想在某个地方放一个DISTINCT ...
How can I do this with MySQL? I think I'm wanting to put a DISTINCT in there somewhere...
此刻的查询就是这样-
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
此外,我是否认为可以通过这种方式将CONCAT与LIKE一起使用?
Additionaly, am I right in thinking I can use CONCAT with LIKE in this way?
(我很欣赏INNER JOIN可能是错误的JOIN类型.实际上我不知道不同的JOIN之间有什么区别.我现在要研究一下!)
(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!)
推荐答案
您可能需要尝试以下操作:
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;
请注意,JOIN
只是INNER JOIN
的同义词.
Note that a JOIN
is just a synonym for INNER JOIN
.
测试用例:
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', 'Jack', 'Black');
结果(不带LIMIT
和WHERE
的查询):
Result (query without the LIMIT
and 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 Jack Black |
+-----------------+
3 rows in set (0.00 sec)
这篇关于MySQL JOIN最近的行而已?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!