full join关联or等值条件改写

full join关联or等值条件改写

2 回答

gbase> create table a(m int,n int);
Query OK, 0 rows affected (Elapsed: 00:00:01.30)
gbase> create table b(m int,n int);
Query OK, 0 rows affected (Elapsed: 00:00:01.40)
gbase> select * from a;
+------+------+
| m    | n    |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    6 |    7 |
|    8 |    9 |
+------+------+
4 rows in set (Elapsed: 00:00:00.00)
gbase> select * from b;
+------+------+
| m    | n    |
+------+------+
|    1 |    3 |
|    4 |    5 |
|    6 |    7 |
+------+------+
3 rows in set (Elapsed: 00:00:00.00)
gbase> select * from a full outer join b on a.m=b.m or a.n=b.n;
ERROR 1708 (HY000): [10.10.10.101:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: This version of GBase doesn't yet support 'OR of full outer join conditions not implemented.'
SQL: SELECT /*10.10.10.101_271_8_2017-05-17_13:02:34*/ /*+ TID('61') */ `_tmp_1695156746_271_t61_1_1494985551_s`.`m` AS `m`, `_tmp_1695156746_271_t61_1_1494985551_s`.`n` AS `n`, `_tmp_1695156746_271_t61_2_1494985551_s`.`c1` AS `c1`, `_tmp_1695156746_271_t61_2_1494985551_s`.`c2` AS `c2` FROM `gctmpdb`._tmp_1695156746_271_t61_1_1494985551_s FULL JOI
gbase>

2018-8-7 10:58
grander 发表于 2018-8-7 10:58
gbase> create table a(m int,n int);Query OK, 0 rows affected (Elapsed: 00:00:01.30)gbase> create tab ...

gbase> select * from a full outer join b on (a.m=b.m)+(a.n=b.n)>0;
+------+------+------+------+
| m    | n    | m    | n    |
+------+------+------+------+
|    1 |    2 |    1 |    3 |
|    2 |    3 |    1 |    3 |
|    6 |    7 |    6 |    7 |
|    8 |    9 | NULL | NULL |
| NULL | NULL |    4 |    5 |
+------+------+------+------+
5 rows in set (Elapsed: 00:00:00.12)

2018-8-7 10:58

撰写回答

您需要登录后才可以回帖 登录 | 立即注册

提问者

发布123
回答331

相关问题

相关资料