mysql - 在别名连接字段中使用 WHERE 会出现 'unknown column' 错误

Question

我有以下查询:

SELECT a.*, CONCAT_WS(' ', `c`.`firstname`, `c`.`lastname`) AS `customer_fullname`
FROM `tickets` a        
LEFT JOIN `customers` `c` ON (`a`.`id_customer` = `c`.`id_customer`)
WHERE a.`id_raffle` = 1  AND `customer_fullname` LIKE '%John%'
ORDER BY a.`id_ticket` ASC LIMIT 0,50

我收到错误:

Unknown column 'customer_fullname' in 'where clause'

有什么建议吗？请注意，我无法重构查询，因为它是由我扩展的类生成的。

Answer 1

不能在 WHERE 子句中使用列别名。

试试这个:

SELECT a.*, CONCAT_WS(' ', `c`.`firstname`, `c`.`lastname`) AS `customer_fullname`
FROM `tickets` a        
LEFT JOIN `customers` `c` ON (`a`.`id_customer` = `c`.`id_customer`)
WHERE a.`id_raffle` = 1  AND CONCAT_WS(' ', `c`.`firstname`, `c`.`lastname`)  LIKE '%John%'
ORDER BY a.`id_ticket` ASC 
LIMIT 0,50

或

SELECT a.*, CONCAT_WS(' ', `c`.`firstname`, `c`.`lastname`) AS `customer_fullname`
FROM `tickets` a        
LEFT JOIN `customers` `c` ON (`a`.`id_customer` = `c`.`id_customer`)
WHERE a.`id_raffle` = 1  AND (`c`.`firstname` LIKE '%John%' OR `c`.`lastname` LIKE '%John%') 
ORDER BY a.`id_ticket` ASC 
LIMIT 0,50