MySQL MySQL选择与CONCAT条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5734570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL select with CONCAT condition
提问by Alex K
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.
我试图在我的脑海中编译这个......我有一个包含名字和姓氏字段的表，我有一个像“Bob Jones”或“Bob Michael Jones”和其他几个字符串。
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 ?
但它说未知列“firstlast”..有人可以帮忙吗？
回答by mdma
The aliases you give are for the output of the query - they are not available within the query itself.
您提供的别名用于查询的输出 - 它们在查询本身中不可用。
You can either repeat the expression:
您可以重复表达式：
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"
or wrap the query
或包装查询
SELECT * FROM (
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users) base
WHERE firstLast = "Bob Michael Jones"
回答by Chandu
Try this:
尝试这个：
SELECT *
FROM (
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
) a
WHERE firstlast = "Bob Michael Jones"
回答by Jeff Swensen
SELECT needefield, CONCAT(firstname, ' ',lastname) as firstlast
FROM users
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"
回答by Viraj Dhamal
Use CONCAT_WS().
使用 CONCAT_WS()。
SELECT CONCAT_WS(' ',firstname,lastname) as firstlast FROM users
WHERE firstlast = "Bob Michael Jones";
The first argument is the separator for the rest of the arguments.
第一个参数是其余参数的分隔符。
回答by RC.
Try:
尝试：
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"
Your alias firstlast is not available in the where clause of the query unless you do the query as a sub-select.
您的别名 firstlast 在查询的 where 子句中不可用，除非您将查询作为子选择进行。
回答by Bogdan
There is an alternative to repeating the CONCATexpression or using subqueries. You can make use of the HAVINGclause, which recognizes column aliases.
除了重复CONCAT表达式或使用子查询，还有一种替代方法。您可以使用HAVING识别列别名的子句。
SELECT
neededfield, CONCAT(firstname, ' ', lastname) AS firstlast
FROM
users
HAVING firstlast = "Bob Michael Jones"
Here is a working SQL Fiddle.
这是一个有效的SQL Fiddle。
