英文:
SQL (Omnisci) get common and uncommon values of a column
问题
我正在使用Omnisci连接两个表格，我需要以下内容：
表格1：
poly_id | num_competitors
1 | 1
2 | 1
3 | 5
表格2：
poly_id | num_stores
1 | 1
5 | 3
7 | 5
我想要的是：
poly_id | num_competitors | num_stores
1 | 1 | 1
2 | 1 | 0
3 | 5 | 0
5 | 0 | 3
7 | 0 | 5
我知道在普通的SQL中，你可以使用FULL JOIN或者UNION来实现，但是Omnisci目前还不支持这些函数（但支持JOIN和LEFT JOIN）。
英文:
I'm using Omnisci to join two tables and I need the following:
Table 1:
poly_id | num_competitors
1 | 1
2 | 1
3 | 5
Table 2:
poly_id | num_stores
1 | 1
5 | 3
7 | 5
What I want:
poly_id | num_competitors | num_stores
1 | 1 | 1
2 | 1 | 0
3 | 5 | 0
5 | 0 | 3
7 | 0 | 5
I know in normal SQL you can do it with FULL JOIN or even with UNION, but Omnisci does not support any of these functions yet (it does support JOIN and LEFT JOIN though).
答案1
得分: 1
我找到了一种解决方法。通过创建一个新的空表来解决。将Table 1和Table 2插入其中，然后按poly id进行分组，以合并具有num_competitors和num_stores的行。
CREATE TABLE competitors_stores (poly_id integer, num_stores integer, num_competitors integer);
INSERT INTO competitors_stores (SELECT poly_id, 0, num_competitors FROM competitors_geo);
INSERT INTO competitors_stores (SELECT poly_id, num_stores, 0 FROM telepi_stores_geo);
CREATE TABLE num_competitors_stores AS (SELECT poly_id, SUM(num_stores) AS num_stores, SUM(num_competitors) AS num_competitors FROM competitors_stores GROUP BY poly_id);
DROP TABLE telepi_competitors_stores;
无论如何，我仍然愿意听取其他解决方案，因为我觉得这不是最佳解决方法。
英文:
I've found a way to solve it. It's by creating a new empty table. Insert into it Table 1 and Table 2 and then make a group by on poly id in order to merge rows that have both num_competitors and num_stores.
CREATE TABLE competitors_stores ( poly_id integer, num_stores integer, num_competitors integer);
INSERT INTO competitors_stores ( SELECT poly_id, 0, num_competitors from competitors_geo)
INSERT INTO competitors_stores ( SELECT poly_id, num_stores, 0 from telepi_stores_geo)
CREATE TABLE num_competitors_stores AS (select poly_id, SUM(num_stores) AS num_stores, SUM(num_competitors) as num_competitors from competitors_stores group by poly_id);
DROP TABLE telepi_competitors_stores;
Anyway, I'm still open to hearing alternatives since I feel like this is not the best way to solve it.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论