MySQL 仅加入最近的一行?

2021-12-17 00:00:00 join sql mysql

我有一个表 customer，其中存储了 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)

相关文章