将两列合并为一列 [英] Combine two columns in one column

问题描述

我有第1列和第2列，并希望将它们合并到同一表的第3列中.如果第2列为空，则显示第1列的值；如果第1列为空，则它们显示第2列的数据.如果它们都为空，则显示为空.我尝试了两件事:

I have column 1 and column 2, and want to combine them into column 3 of the same table. If the column 2 is null then display column 1 value, if column 1 is null, them display column 2 data. If both of them are null, then display null. I tried two things:

1)使用CONCAT SELECT CONCAT(Column1, Column2) AS Column3 FROM TEST_ATTRIBUTES.

1) using CONCAT SELECT CONCAT(Column1, Column2) AS Column3 FROM TEST_ATTRIBUTES.

仅当两个列都不为null时，它才合并列.否则，它只是将null声明为空.

It just merges the columns only when both of them are not null. otherwise it just prins null.

2)使用(第1列+第2列).

2) using (column1 + column 2).

SELECT (Column1 + Column2) AS Column3 FROM TEST_ATTRIBUTES.

未显示所需的输出.

我正在用Java编写此代码. 谢谢

I'm writing this code in java. Thanks

推荐答案

使用COALESCE()，这不会连接，但会返回列表中的第一个非空值.

use COALESCE(), this doesn't concatenate but returns the first non-null value from the list.

SELECT Column1, 
       Column2, 
       COALESCE(Column1, Column2) AS Column3 
FROM   TEST_ATTRIBUTES

• SQLFiddle演示
• SQLFiddle Demo

如果它们都有可能为空，

if there are chances that both of them are null,

SELECT Column1, 
       Column2, 
       IF(Column1 IS NULL AND Column2 IS NULL, NULL, CONCAT(COALESCE(Column1,''), COALESCE(Column2,''))) AS Column3 
FROM   TEST_ATTRIBUTES

• SQLFiddle演示
• SQLFiddle Demo

这篇关于将两列合并为一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！