MySQL select with CONCAT condition

I'm trying to compile this in my mind.. i have a table with firstname and lastname fields and i have a string like "Bob Jones" or "Bob Michael Jones" and several others.

the thing is, i have for example Bob in firstname, and Michael Jones in lastname

so i'm trying to

SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
WHERE firstlast = "Bob Michael Jones"

but it says unknown column "firstlast".. can anyone help please ?

306159 次浏览

小开

SELECT needefield, CONCAT(firstname, ' ',lastname) as firstlast
FROM users
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"

小开

试试:

SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"

您的别名 first last 在查询的 where 子句中不可用，除非您将查询作为子选择执行。

小开

试试这个:

SELECT *
FROM  (
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
) a
WHERE firstlast = "Bob Michael Jones"

小开

最佳答案

您提供的别名用于查询的输出——它们在查询本身中不可用。

你可以重复这句话:

SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"

或包装查询

SELECT * FROM (
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users) base
WHERE firstLast = "Bob Michael Jones"

小开

除了重复 CONCAT表达式或使用子查询之外，还有一种替代方法。您可以使用 HAVING子句，它可以识别列别名。

SELECT
neededfield, CONCAT(firstname, ' ', lastname) AS firstlast
FROM
users
HAVING firstlast = "Bob Michael Jones"

Here is a working SQL Fiddle.

小开

使用 CONCAT _ WS ()。

SELECT CONCAT_WS(' ',firstname,lastname) as firstlast FROM users
WHERE firstlast = "Bob Michael Jones";

第一个参数是其余参数的分隔符。