MySQL JOIN最近的行而已？

MySQL JOIN最近的行而已？

您可能需要尝试以下方法：

SELECt    CONCAt(title, ' ', forename, ' ', surname) AS nameFROM      customer cJOIN      (   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;

请注意，a

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 nameFROM      customer cJOIN      (   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)