MySQL查询将两列合并为一个列？
您可以COALESCE()为此使用功能。在该COALESCE()函数中，它从列中返回第一个NONNULL值。为了理解这个概念，让我们首先创建一个演示表
mysql> create table combineTwoColumnsDemo -> ( -> UserId int, -> UserName varchar(20), -> UserAge int -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into combineTwoColumnsDemo values(101,'John',23); mysql> insert into combineTwoColumnsDemo values(102,'Carol',20); mysql> insert into combineTwoColumnsDemo values(103,'Bob',25); mysql> insert into combineTwoColumnsDemo values(104,'Mike',26); mysql> insert into combineTwoColumnsDemo values(105,NULL,23); mysql> insert into combineTwoColumnsDemo values(105,'Maxwell',NULL);
现在，您可以使用select语句显示表中的所有记录。查询如下-
mysql> select *from combineTwoColumnsDemo;
以下是输出
+--------+----------+---------+ | UserId | UserName | UserAge | +--------+----------+---------+ | 101 | John | 23 | | 102 | Carol | 20 | | 103 | Bob | 25 | | 104 | Mike | 26 | | 105 | NULL | 23 | | 105 | Maxwell | NULL | +--------+----------+---------+ 6 rows in set (0.00 sec)
这是将两列合并为一个查询的查询
mysql> SELECT UserName, -> UserAge, -> COALESCE(UserName, UserAge) AS Combine_UserName_UserAge -> FROM combineTwoColumnsDemo;
以下是输出
+----------+---------+--------------------------+ | UserName | UserAge | Combine_UserName_UserAge | +----------+---------+--------------------------+ | John | 23 | John | | Carol | 20 | Carol | | Bob | 25 | Bob | | Mike | 26 | Mike | | NULL | 23 | 23 | | Maxwell | NULL | Maxwell | +----------+---------+--------------------------+ 6 rows in set (0.00 sec)