如何在MYSQL查询中获得全名和姓?

I want to be able to do something like this

我想做这样的事情。

SELECT `first_name` + " " + `last_name` as `whole_name` FROM `users`

So basically I get one column back whole_name which is first_name and last_name concatenated together with a (space).

基本上，我得到了一个列whole_name它是first_name和last_name与一个(空格)连接在一起。

How do I do that in SQL, or more specifically, MySQL ?

我怎么用SQL，或者更具体地说，MySQL ?

3 个解决方案

#1

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html function_concat-ws

SELECT CONCAT_WS(" ", `first_name`, `last_name`) AS `whole_name` FROM `users`

#2

When you have three columns : first_name, last_name, mid_name:

当您有三个列:first_name、last_name、mid_name:

SELECT CASE 
    WHEN mid_name IS NULL OR TRIM(mid_name) ='' THEN
        CONCAT_WS( " ", first_name, last_name ) 
    ELSE 
        CONCAT_WS( " ", first_name, mid_name, last_name ) 
    END 
FROM USER;

#3

You can use a query to get the same:

您可以使用查询获得相同的结果:

SELECT CONCAT(FirstName , ' ' , MiddleName , ' ' , Lastname) AS Name FROM TableName;

Note: This query return if all columns have some value if anyone is null or empty then it will return null for all, means Name will return "NULL"

注意:这个查询返回如果所有列都有某个值如果有人是null或empty那么它将返回null，这意味着Name将返回" null "

To avoid above we can use the IsNull keyword to get the same.

为了避免上述情况，我们可以使用IsNull关键字来获得相同的结果。

SELECT Concat(Ifnull(FirstName,' ') ,' ', Ifnull(MiddleName,' '),' ', Ifnull(Lastname,' ')) FROM TableName;

If anyone containing null value the ' ' (space) will add with next value.

如果任何人包含空值，“”(空格)将添加下一个值。

#1