在mySQL中将到列合并为一个新列

Question

在mySQL中将2列合并为单列有问题。

这就是我想要的：

从这里开始

+----------------+----------------+----------+
|    male        |     female     |   gender |
+----------------+----------------+----------+
|    male        |      null      |   null   |
+----------------+----------------+----------+
|    male        |      null      |   null   |
+----------------+----------------+----------+
|    null        |    female      |   null   |
+----------------+----------------+----------+
|    male        |      null      |   null   |
+----------------+----------------+----------+

到这个

+----------+
|   gender |
+----------+
|   male   |
+----------+
|   male   |
+----------+
|  female  |
+----------+
|   male   |
+----------+

我用过：

SELECT male, 
   female, 
   COALESCE(male, female) AS gender 
FROM nrco_ldap

但在性别专栏里什么都没有发生。

Answer 1

您正在尝试更新一个现有的列，因此select显然不是实现它的方法。因此，您需要编写如下所示的update查询：

update nrco_ldap
set gender = coalesce(male,female)

如果除了填充gender列之外，还想删除male和female列，则需要编写如下所示的适当的drop语句：

alter table nrco_ldap drop column male;
alter table nrco_ldap drop column female;