SQL查询两个表之间的差异

Question

我有两个表:Bucket1 和 Bucket2。

两个表中的列均为:ToyId 和 Price

Bucket1
-----------------------------
ToyId        |    Price
-----------------------------
A            |    10
B            |    20
C            |    30
D            |    40
E            |    50
-----------------------------

Bucket2
-----------------------------
ToyId        |    Price
-----------------------------
D            |    45
E            |    50
F            |    60
G            |    70
H            |    80
-----------------------------

我想要一个如下的结果表:

Result
-----------------------------------------------------------
ToyId        |    PriceTab1    |    PriceTab2   |   Diff
-----------------------------------------------------------
A            |    10           |    NA          |    NA
B            |    20           |    NA          |    NA
C            |    30           |    NA          |    NA
D            |    40           |    45          |    5
E            |    50           |    50          |    0
F            |    NA           |    80          |    NA
G            |    NA           |    90          |    NA
H            |    NA           |    100         |    NA
-----------------------------------------------------------

上表包含:1)普通玩具(D、E)

2) 玩具在 Bucket1 中但不在 Bucket2 中(A、B、C)

3) 玩具在 Bucket2 中但不在 Bucket3 中(F、G、H)

4) 价格差异(如适用)(D、E)

是否可以在单个查询中实现此目的？

感谢您的阅读!

Answer 1

表格

create table b1 (
  t_id varchar(1),
  price int
  );

  create table b2 (
  t_id varchar(1),
  price int
  );

 insert into b1 (t_id, price)
             values
             ('a', 10),('b', 20),('c', 30),('d', 40),('e', 50);
insert into b2 (t_id, price)
             values
             ('d', 45),('e', 50),('f', 60),('g', 70),('h', 80);  

选择

select t_id, sum(price1), sum(price2) 
from 
  (select t_id, price as price1, null as price2 from b1
   union all 
   select t_id, null as price1, price as price2 from b2) res
group by t_id;

将 diff 计算排除在范围之外

http://sqlfiddle.com/#!2/c669fc/6