搜索连接字段 [英] Searching concatenated field

问题描述

SELECT `users`.`firstname`, `users`.`lastname`, `users`.`id`, 
CONCAT(firstname," " ,lastname) AS `users.firstnamelastname`, 
CONCAT(lastname," " ,firstname) AS `users.lastnamefirstname` 
FROM `users` 
WHERE (id != '15') 
AND (firstnamelastname like 'a%' ) 
OR (lastnamefirstname like 'a%')

上面的代码我用来搜索一个连续的字段，但上面的代码不起作用.有没有 sql 高手帮帮我.

The above code I am using to search against a concated field but the above is not working. Is there anyone sql guru to help me out.

推荐答案

SELECT *
FROM 
    (
    SELECT `users`.`firstname`, `users`.`lastname`, `users`.`id`, 
    CONCAT(firstname," " ,lastname) AS `firstnamelastname`, 
    CONCAT(lastname," " ,firstname) AS `lastnamefirstname` 
    FROM `users` 
    WHERE (id != '15')
)
WHERE (firstnamelastname like 'a%' ) 
OR (lastnamefirstname like 'a%')

当 MySQL 首次评估 WHERE 子句时，您的别名对它不可用.

Your aliases are not available to MySQL when it's first evaluating the WHERE clause.

但您的查询实际上等于:

But your query actually equals this:

SELECT `users`.`firstname`, `users`.`lastname`, `users`.`id`, 
CONCAT(firstname," " ,lastname) AS `firstnamelastname`, 
CONCAT(lastname," " ,firstname) AS `lastnamefirstname` 
FROM `users` 
WHERE (id != '15')
AND (firstname LIKE 'a%' OR lastname LIKE 'a%')

所以你在 where 子句中的 concat 是不必要的.

So your concat in the where clause is unnecessary.

这篇关于搜索连接字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！