MySQL 只加入最近的行

发布于09月01日

我有一个表客户，存储客户id、邮箱和参考资料.另外还有一个表customer_data，用于存储对客户所做更改的历史记录，即当发生更改时，插入新行.

为了在表中显示客户信息，需要将这两个表连接起来，但是，只有customer_数据中最近的一行应该连接到customer表.

由于查询是分页的，所以有一个限制和一个偏移量，所以它变得更加复杂.

我怎样才能用MySQL做到这一点？我想我想在那里的某个地方放一个独特的...

现在的问题是这样的-

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吗？

(我知道内部连接可能是错误的连接类型.实际上，我不知道不同连接之间有什么区别.我现在要研究一下！)

推荐答案

您可能需要try 以下方法:

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的同义词.

测试用例:

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的查询):

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)