SQL按名称对类似的表进行分组。

Question

我有两张相似的桌子

表1

  | id | name | amount|
  | 2  | Mike | 1000  |
  | 3  | Dave | 2500  |

表2

  | id | name | amount|
  | 2  | Mike | 1200  |
  | 4  | James| 2500  |

我想查询这些表以获得如下结果：

  | id | name | amount_table1| amount_table2|
  | 2  | Mike | 1000         | 1200         |
  | 3  | Dave | 2500         |              |
  | 4  | james|              | 2500         |

Answer 1

UNION ALL表。执行GROUP BY以获取每个id/名称组合中的一行。

select id, name, sum(amount1), sum(amount2)
from
(
    select id, name, amount as amount1, null as amount2 from table1
    union all
    select id, name, null, amount  from table2
) dt
group by id, name

Answer 2

你需要与左右联合做联合

select a.id , a.name , a.amount amount_table1,b.amount amount_table2 from table1 a left join table2 b on (a.id=b.id)
union
select b.id , b.name ,a.amount,b.amount from table1 a right join table2 b on (a.id=b.id)

Answer 3

MySql不支持完全外部连接。

但它支持左、右联合和联合。

select 
t1.id, t1.name, t1.amount as amount_table1, t2.amount as amount_table2
from Table1 t1
left join Table2 t2 on t1.id = t2.id

union all

select t2.id, t2.name, t1.amount, t2.amount
from Table2 t2
left join Table1 t1 on t2.id = t1.id
where t1.id is null

第一个选择将得到那些只在Table1和那些在这两个。

第二个select将只在Table2中获得这些。

而联盟将这些结果结合在一起。

如果这是一个支持完全连接的数据库，那么它将简化为：

select 
coalesce(t1.id, t2.id) as id, 
coalesce(t1.name, t2.name) as name, 
t1.amount as amount_table1, 
t2.amount as amount_table2
from Table1 t1
full join Table2 t2 on t1.id = t2.id