MySQLJOIN 只是最近的一行吗？

我有一个表客户，它存储了 customer _ id、电子邮件和引用。还有一个附加的表 customer _ data，它存储对客户所做更改的历史记录，也就是说，当发生更改时插入一个新行。

为了在表中显示客户信息，需要将两个表连接起来，但是只有 customer _ data 中最近的一行才应该连接到 customer 表。

由于查询是分页的，因此有一个限制和偏移量，所以情况稍微复杂一些。

我如何使用 MySQL 做到这一点? 我认为我想把一个 DISTINCT 放在那里的某个地方..。

此时的查询是这样的-

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

另外，我认为我可以这样使用与 LIKE 的 CONCAT，这样想对吗？

(我明白内部连接可能是使用错误类型的连接。实际上，我不知道不同的 JOIN 之间有什么区别。我现在就去调查一下!)

152245 次浏览
小开 
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%'

我觉得你该换衣服了 C.customer _ id 到 c.id

Else 更新表结构

小开

将实际数据记录到“ Customer _ data”表中是一个好主意。使用这些数据，您可以根据需要从“ customer _ data”表中选择所有数据。

小开
最佳答案

你可以试试下面的方法:

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)
小开

假设 customer_data中的自动递增列名为 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
小开

对于任何必须使用旧版本 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;

本质上，这就是找到将数据表连接到客户的最大 id，然后将数据表连接到找到的最大 id。这样做的原因是，选择组的 max 并不能保证其余数据与 id 匹配，除非您将其重新连接到自身上。

我还没有在新版本的 MySQL 上测试过，但它可以在4.0.30上运行。

小开

如果处理的是大量查询，那么最好将最新行的请求移动到 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
)
小开

你也可以这么做

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;
小开

我知道这个问题很老了，但是这些年来它得到了很多关注，我认为它缺少了一个概念，这个概念可能对类似案例中的人有所帮助。为了完整起见，我把它添加到这里。

如果您不能修改原来的数据库模式，那么已经提供了许多很好的答案，并且很好地解决了问题。

但是，如果您修改您的模式，我建议您在 customer表中添加一个字段，该字段保存该客户的最新 customer_data记录的 id:

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
);

询问顾客

查询非常简单快捷:

SELECT c.*, d.title, d.forename, d.surname
FROM customer c
INNER JOIN customer_data d on d.id = c.current_data_id
WHERE ...;

缺点是在创建或更新客户时额外的复杂性。

更新客户信息

无论何时您想更新客户，都可以在 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;

创造一个顾客

创建客户只需要插入 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;

收工

创建/更新客户的额外复杂性可能令人生畏，但可以通过触发器轻松实现自动化。

最后，如果您正在使用 ORM，那么这可能非常容易管理。ORM 可以为您处理插入值、更新 id 和自动连接两个表的问题。

下面是您的可变 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();
}
}

还有不可变的 CustomerData模型，它只包含 getter:

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;
}
}
小开

简单的解决方案左加入1最/最近的行是使用选择在 ON 短语

SELECT *
FROM A
LEFT JOIN B
ON A.id = (SELECT MAX(id) FROM B WHERE id = A.id)

其中 A.id 是自动增量主键。


提交答案