MySQL 查询将两列合并为一列?
mysqlmysqli database
您可以使用 COALESCE() 函数来实现这一点。在 COALESCE() 函数中，它返回列中的第一个非空值。为了理解这个概念，让我们首先创建一个演示表
mysql> create table combineTwoColumnsDemo -> ( -> UserId int, -> UserName varchar(20), -> UserAge int -> ); Query OK, 0 rows affected (1.12 sec)
使用 insert 命令在表中插入一些记录。 查询语句如下 −
mysql> insert into combineTwoColumnsDemo values(101,'John',23); Query OK, 1 row affected (0.16 sec) mysql> insert into combineTwoColumnsDemo values(102,'Carol',20); Query OK, 1 row affected (0.14 sec) mysql> insert into combineTwoColumnsDemo values(103,'Bob',25); Query OK, 1 row affected (0.13 sec) mysql> insert into combineTwoColumnsDemo values(104,'Mike',26); Query OK, 1 row affected (0.18 sec) mysql> insert into combineTwoColumnsDemo values(105,NULL,23); Query OK, 1 row affected (0.22 sec) mysql> insert into combineTwoColumnsDemo values(105,'Maxwell',NULL); Query OK, 1 row affected (0.15 sec)
现在，您可以使用 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)
