mysql - 在 MySQL 中构建查询来查找客户名称

Question

我在 MySQL 中有以下 SQL 语句，但问题是它只接受一个字符串，所以如果我有一位名叫 John Mathews 的客户，他们可以输入 jo 或 joh 或 john 来查找它，但当他们输入 john ma 或 john mat 时，它将返回零行。同样，当他们输入 john 4455667(其中 4455667 是联系人详细信息)时。

请问我该如何解决？

SELECT DISTINCT customers.customer_id, CONCAT(IFNULL(titles.title_name, ""), " " , customers.first_name, " ", customers.middle_name, " ", customers.last_name) AS customer_name, MAX(DATE(advance_orders.delivery_date)) AS last_order
FROM customers
LEFT JOIN titles ON titles.title_id = customers.title
LEFT JOIN companies ON companies.company_id = customers.company
LEFT JOIN job_titles ON job_titles.job_title_id = customers.job_title
LEFT JOIN customers_contact ON customers_contact.customer_id = customers.customer_id
LEFT JOIN advance_orders ON advance_orders.customer_id = customers.customer_id
WHERE first_name LIKE CONCAT('%', param_find ,'%')
OR middle_name LIKE CONCAT('%', param_find ,'%')
OR last_name LIKE CONCAT('%', param_find ,'%')
OR customers_contact.contact_details LIKE CONCAT('%', param_find ,'%')
OR companies.company_name LIKE CONCAT('%', param_find ,'%')
OR job_titles.job_title_name LIKE CONCAT('%', param_find ,'%')
GROUP BY customers.customer_id
ORDER BY customer_name;

Answer 1

而不是:

WHERE first_name LIKE CONCAT('%', param_find ,'%')
OR middle_name LIKE CONCAT('%', param_find ,'%')
OR last_name LIKE CONCAT('%', param_find ,'%')

尝试:

WHERE CONCAT(first_name, ' ', last_name) LIKE CONCAT('%', param_find ,'%')
OR CONCAT(first_name, ' ',middle_name, ' ', last_name) LIKE CONCAT('%', param_find ,'%')

当然，您可以将逻辑扩展到其他 OR:

OR customers_contact.contact_details LIKE CONCAT('%', param_find ,'%')
OR companies.company_name LIKE CONCAT('%', param_find ,'%')
OR job_titles.job_title_name LIKE CONCAT('%', param_find ,'%')

如果你需要的话。

问候