我需要加入2个数据库,每个数据库有2个表 [英] I need to join 2 databases, with 2 tables each
问题描述
亲爱的, 我有一个SQL问题.我需要加入2个数据库,每个数据库有2个表.我这里有数据库表的图片.非常感谢您的帮助.
Dears, I have an SQL problem. I need to join 2 databases, with 2 tables each. I have the pictures of the tables of the databases here. Thank you very much for your helps.
推荐答案
使用UNION ALL,您可以从2个选择中获得1个组合结果集.
然后,您可以将其分组并总计每个日期的金额.
With a UNION ALL you can get 1 combined resultset from 2 selects.
Then you can group that and SUM the amounts per date.
所以您可能正在寻找这样的东西:
So you're probably looking for something like this:
select
q.ID,
q.Name,
nullif(sum(case when q.Date = '2018-05-01' then q.Amount end), 0) as "5/1/2018",
nullif(sum(case when q.Date = '2018-05-02' then q.Amount end), 0) as "5/2/2018"
from
(
select u1.ID, u1.Name, a1.Date, a1.Amount
from DB1.Table1 AS u1
join DB1.Table2 AS a1 on (a1.ID = u1.ID and a1.Amount is not null)
where a1.Date IN ('2018-05-01', '2018-05-02')
union all -- combines the results of the 2 selects into one resultset
select u2.ID, u2.Name, a2.Date, a2.Amount
from DB2.Table1 AS u2
join DB2.Table2 AS a2 on (a2.ID = u2.ID and a2.Amount is not null)
where a2.Date IN ('2018-05-01', '2018-05-02')
) AS q
group by q.ID, q.Name
order by q.ID;
另一种方法是将它们全部加入.
An alternative is to JOIN them all up.
select
coalesce(a1.ID, a2.ID) as ID,
max(coalesce(u1.Name, u2.Name)) as Name,
max(case
when coalesce(a1.Date, a2.Date) = '2018-05-01'
and coalesce(a1.Amount, a2.Amount) is not null
then coalesce(a1.Amount, 0) + coalesce(a2.Amount, 0)
end) as "5/1/2018",
max(case
when coalesce(a1.Date, a2.Date) = '2018-05-02'
and coalesce(a1.Amount, a2.Amount) is not null
then coalesce(a1.Amount, 0) + coalesce(a2.Amount, 0)
end) as "5/2/2018"
from DB1.Table2 AS a1
full join DB2.Table2 AS a2 on (a2.ID = a1.ID and a2.Date = a1.Date)
left join DB1.Table1 AS u1 on (u1.ID = a1.ID)
left join DB2.Table1 AS u2 on (u2.ID = a2.ID)
where coalesce(a1.Date, a2.Date) IN ('2018-05-01', '2018-05-02')
group by coalesce(a1.ID, a2.ID)
order by coalesce(a1.ID, a2.ID);
但是请注意,这样一来,假设两个Table2在(ID,Date)上具有唯一性
But then note that this way, that there's an assumption that the two Table2 have a uniqueness on (ID, Date)
T-Sql测试数据: 声明@ DB1_Table1表(id int,名称varchar(30)); 声明@ DB2_Table1表(id int,名称varchar(30)); 声明@ DB1_Table2表(id int,[Date]日期,金额小数(8,2)); 声明@ DB2_Table2表(id int,[Date]日期,金额小数(8,2)); 插入@ DB1_Table1(id,Name)值(1,'Susan'),(2,'Juan'),(3,'Tracy'),(4,'Jenny'),(5,'Bill'); 插入@ DB2_Table1(id,Name)值(1,'Susan'),(2,'Juan'),(3,'Tracy'),(4,'Jenny'),(5,'Bill'); 插入@ DB1_Table2(id,[Date],Amount)值 (1,'2018-05-01',20),(2,'2018-05-01',null),(3,'2018-05-01',30),(4,'2018-05- 01',50),(5,'2018-05-01',null), (1,'2018-05-02',15),(2,'2018-05-02',40),(3,'2018-05-02',25),(4,'2018-05- 02',8),(5,'2018-05-02',null); 插入@ DB2_Table2(id,[Date],Amount)值 (1,'2018-05-01',null),(2,'2018-05-01',15),(3,'2018-05-01',20),(4,'2018-05- 01',10),(5,'2018-05-01',null), (1,'2018-05-02',15),(2,'2018-05-02',30),(3,'2018-05-02',35),(4,'2018-05- 02',null),(5,'2018-05-02',30);
T-Sql test data: declare @DB1_Table1 table (id int, Name varchar(30)); declare @DB2_Table1 table (id int, Name varchar(30)); declare @DB1_Table2 table (id int, [Date] date, Amount decimal(8,2)); declare @DB2_Table2 table (id int, [Date] date, Amount decimal(8,2)); insert into @DB1_Table1 (id, Name) values (1,'Susan'),(2,'Juan'),(3,'Tracy'),(4,'Jenny'),(5,'Bill'); insert into @DB2_Table1 (id, Name) values (1,'Susan'),(2,'Juan'),(3,'Tracy'),(4,'Jenny'),(5,'Bill'); insert into @DB1_Table2 (id, [Date], Amount) values (1,'2018-05-01',20),(2,'2018-05-01',null),(3,'2018-05-01',30),(4,'2018-05-01',50),(5,'2018-05-01',null), (1,'2018-05-02',15),(2,'2018-05-02',40),(3,'2018-05-02',25),(4,'2018-05-02',8),(5,'2018-05-02',null); insert into @DB2_Table2 (id, [Date], Amount) values (1,'2018-05-01',null),(2,'2018-05-01',15),(3,'2018-05-01',20),(4,'2018-05-01',10),(5,'2018-05-01',null), (1,'2018-05-02',15),(2,'2018-05-02',30),(3,'2018-05-02',35),(4,'2018-05-02',null),(5,'2018-05-02',30);
这篇关于我需要加入2个数据库,每个数据库有2个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!