搜索Concat字符串而不是worknig

Question

我有一个查询，用于搜索客户的姓氏，而且效果很好：

SELECT `pt_bookings`.`booking_id`,`pt_bookings`.`booking_date`,
pt_bookings.booking_ref_no, `ai_last_name`,`pt_bookings`.`booking_checkout`,`pt_bookings`.`booking_checkin` FROM (`pt_bookings`) INNER join pt_accounts on pt_accounts.accounts_id = pt_bookings.accounts_id AND pt_accounts.accounts_type='customers'WHERE ai_last_name LIKE CONCAT('%jansen%')

但是我很想搜索一个由3个连接的varchars ai_firstname ai_middle_name ai_last_name组成的字符串，我希望使用这个查询，但是它返回0记录，不会出现任何错误。这个查询有什么问题吗？特克斯！

SELECT `pt_bookings`.`booking_id`,`pt_bookings`.`booking_date`,  pt_bookings.booking_ref_no, CONCAT(`pt_accounts`.`ai_first_name`,`pt_accounts`.`ai_middle_name`,`pt_accounts`.`ai_last_name`) as customer_name,  `pt_bookings`.`booking_checkout`,`pt_bookings`.`booking_checkin`FROM (`pt_bookings`) INNER join pt_accounts on pt_accounts.accounts_id = pt_bookings.accounts_id AND pt_accounts.accounts_type='customers'WHERE  customer_name LIKE  CONCAT('%jansen%')

Answer 1

这是您的查询：

SELECT b.`booking_id`, b.`booking_date`,  b.booking_ref_no, 
       CONCAT(`a.`ai_first_name`, a.`ai_middle_name`, a.`ai_last_name`) as customer_name,
      b.`booking_checkout`, b.`booking_checkin`
FROM pt_bookings b INNER join
     pt_accounts a
     on a.accounts_id = b.accounts_id AND
        a.accounts_type = 'customers'
WHERE customer_name LIKE  CONCAT('%jansen%');

主要问题是customer_name是列别名，因此不能在WHERE中使用它。MySQL有一个方便的扩展，允许在HAVING子句中使用这个逻辑：

HAVING customer_name LIKE '%jansen%'

我不知道CONCAT()是干什么用的。没有理由用一个论点来使用它。

Answer 2

试试这个：

SELECT b.`booking_id`, b.`booking_date`,  b.booking_ref_no, 
       CONCAT(`a.`ai_first_name`, a.`ai_middle_name`, a.`ai_last_name`) as customer_name,
      b.`booking_checkout`, b.`booking_checkin`
FROM pt_bookings b INNER join
     pt_accounts a
     on a.accounts_id = b.accounts_id AND
        a.accounts_type = 'customers'
WHERE
  CONCAT(ai_firstname, ai_middle_name, ai_last_name)
  LIKE '%jansen%';