sqlite - 通过聚合联合多个表和组(SQlite)

Question

我有这样的数据:
table1

id | part  | price
1  | ox900 | 100
2  | ox980 | 200

和
table2

id | part  | price
1  | ox560 | 560
2  | ox980 | 120

结果我想得到这样的模式:

id | part  | priceTable1 | priceTable2 | minPrice
1  | ox900 | 100         |             | 100
1  | ox980 | 200         | 120         | 120
1  | ox560 |             | 560         | 560

为了简化它可以没有 minPrice柱子...

现在我有这样的查询:

SELECT  *
FROM (select part, price from supportContacts
union all
select part, price from supportContacts2)
group by part

但这并不是我想要实现的。

有没有可能以某种方式做，我上面描述的？

也是一个 fiddle : http://sqlfiddle.com/#!7/f7401/7/0

Answer 1

SQLite 不支持全外连接，所以先获取所有部分的列表，然后用左外连接查找它们的价格:

SELECT part,
       table1.price AS priceTable1,
       table2.price AS priceTable2,
       min(ifnull(table1.price, 'inf'),
           ifnull(table2.price, 'inf')) AS minPrice
FROM (SELECT part FROM table1
      UNION
      SELECT part FROM table2)
LEFT JOIN table1 USING (part)
LEFT JOIN table2 USING (part);

( fiddle)