请我需要一种方法来组合两列从同一个表。我有一张table,比如说我的table。我只提取了几个我感兴趣的列,如下表所示,使用以下语句:
select distinct ONES, TWOS, TYPE, THREES, FOURS, FIRST, LAST, LEG_1, LEG_2, DATE from my_Table;
结果如下:
+-----+-----+----+-------+-------+------+-----+------+------+-----------+
|ONES |TWOS |TYPE|THREES |FOURS |FIRST |LAST |LEG_1 |LEG_2 |DATE |
+-----+-----+----+-------+-------+------+-----+------+------+-----------+
|AAAA1|AAAA2|O |AAAA3 |null |null |null |INC |WELL |null |
|BBBB1|BBBB2|P |null |BBBB4 |DALE |BETY |null |null |2020-01-01 |
|CCCC1|CCCC2|P |null |CCCC4 |KENT |SALA |null |null |2018-10-18 |
|DDDD1|DDDD2|P |null |DDDD4 |KYLE |GIRE |null |null |2020-04-24 |
+-----+-----+----+-------+-------+------+-----+------+------+-----------+
我执行union操作,以便将列“threes”和“fours”合并到列“both”中,如下面的select语句所示,
select * from (select distinct ONES, TWOS, TYPE, THREES as BOTH, FIRST, LAST, LEG_1, LEG_2, DATE from my_Table) UNION (select distinct ONES, TWOS, TYPE, FOURS as BOTH, FIRST, LAST, LEG_1, LEG_2, DATE from my_Table)
得到以下结果
+-----+-----+----+-----+-----+-----+------+-----+-----------+
|ONES |TWOS |TYPE|BOTH |FIRST|LAST |LEG_1 |LEG_2|DATE |
+-----+-----+----+-----+-----+-----+------+-----+-----------+
|CCCC1|CCCC2|P |null |KENT |SALA |null |null |2018-10-18 |
|AAAA1|AAAA2|O |null |null |null |INC |WELL |null |
|AAAA1|AAAA2|O |AAAA3|null |null |INC |WELL |null |
|BBBB1|BBBB2|P |null |DALE |BETY |null |null |2020-01-01 |
|DDDD1|DDDD2|P |DDDD4|KYLE |GIRE |null |null |2020-04-24 |
|DDDD1|DDDD2|P |null |KYLE |GIRE |null |null |2020-04-24 |
|CCCC1|CCCC2|P |CCCC4|KENT |SALA |null |null |2018-10-18 |
|BBBB1|BBBB2|P |BBBB4|DALE |BETY |null |null |2020-01-01 |
+-----+-----+--- +-----+-----+-----+------+-----+-----------+
我如何执行操作,使我的最终结果如下所示
+-----+-----+----+-----+-----+-----+------+-----+-----------+
|ONES |TWOS |TYPE|BOTH |FIRST|LAST |LEG_1 |LEG_2|DATE |
+-----+-----+----+-----+-----+-----+------+-----+-----------+
|AAAA1|AAAA2|O |AAAA3|null |null |INC |WELL |null |
|DDDD1|DDDD2|P |DDDD4|KYLE |GIRE |null |null |2020-04-24 |
|CCCC1|CCCC2|P |CCCC4|KENT |SALA |null |null |2018-10-18 |
|BBBB1|BBBB2|P |BBBB4|DALE |BETY |null |null |2020-01-01 |
+-----+-----+--- +-----+-----+-----+------+-----+-----------+
谢谢您
3条答案
按热度按时间pepwfjgg1#
使用
coalesce
选择第一个非空值。5ssjco0h2#
似乎两列中都没有非null,所以我认为您只需要将两列合并在一起:
sg2wtvxw3#
你可以把最后一句话说清楚,