在表之间使用双连接的SQL视图。

I'm trying to create a view using a double join between tables.

我尝试使用两个表之间的双连接创建一个视图。

I'm working on some travel software, managing holiday bookings. The different items a person pays for can be in different currencies.

我正在做一些旅行软件，管理假日预订。一个人支付的不同商品可以是不同的货币。

I've a table of bookings, and a table of currencies.

我有一张预定表和一张货币表。

There are many different items a person can pay for, all stored in different tables. I've created a view showing the total owed per payment Item type.

有许多不同的项目，一个人可以支付，所有存储在不同的表格。我创建了一个视图，显示每个支付项类型的总欠款。

e.g. owed for Transfers:

例如欠传输:

BookingID  CurrencyID  TotalTransfersPrice
1          1           340.00
2          1           120.00
2          2           100.00

e.g. owed for Extras:

如额外欠:

BookingID  CurrencyID  TotalExtrasPrice
1          1           200.00
1          2           440.00
2          1           310.00

All is good so far.

到目前为止一切都很好。

What I'd like to do is to create a master view that brings this all together:

我想要做的是创建一个主视图，将所有这些集合在一起:

BookingID  CurrencyID  TotalExtrasPrice  TotalTransfersPrice
1          1           200.00            340.00
1          2           440.00            NULL
2          1           310.00            120.00
2          2           NULL              100.00

I can't figure out how to make the above. I've been experimenting with double joins, as I'm guessing I need to do joins both for the BookingID and the CurrencyID?

我想不出怎么做上述的事。我一直在尝试双连接，我猜我需要加入BookingID和CurrencyID的连接?

Any ideas?

什么好主意吗?

Thanks!

谢谢!

Phil.

菲尔。

5 个解决方案

#1

For SQL Server

对SQL Server

This query allows each {BookingId, CurrencyId} have more than one row in the Transfer and Extras tables.

这个查询允许每个{BookingId, CurrencyId}在传输和临时表中有多个行。

since you stated

既然你说

I've created a view showing the total owed per payment Item type.

我创建了一个视图，显示每个支付项类型的总欠款。

I'm accumulating them by BookinID and CurrencyID

我用BookinID和CurrencyID来积累它们。

 SELECT ISNULL(transfers.BookingId, extras.BookingId) AS BookingId,  
       ISNULL(transfers.CurrencyId, extras.CurrencyId) AS CurrencyId,
       SUM(TotalExtrasPrice) AS TotalExtrasPrice,
       SUM(t.TotalTransfersPrice) AS TotalTransfersPrice
FROM transfers
FULL OUTER JOIN extras ON transfers.BookingId = extras.BookingId and transfers.CurrencyId = extras.CurrencyId
GROUP BY ISNULL(transfers.BookingId, extras.BookingId),ISNULL(transfers.CurrencyId, extras.CurrencyId)

#2

You should try to use the full outer join in joining the two tables: Transfers & Extras. Assuming you are using MySQL platform, the sql query can be:

您应该尝试使用完整的外部连接来连接两个表:传输和附加。假设您正在使用MySQL平台，那么sql查询可以是:

SELECT t.BookingId,t.CurrencyId,e.TotalExtrasPrice,t.TotalTransfersPrice
FROM transfers as t FULL OUTER JOIN extras as e
ON t.BookingId = e.BookingId AND t.CurrencyId = e.CurrencyId;

#3

use joins

使用连接

select t.BookingId,t.CurrencyId,e.TotalExtrasPrice,t.TotalTransfersPrice
from transfers as t
join extras as e
on t.BookingId = e.BookingId and t.CurrencyId = e.CurrencyId

#4

If you want to cover the case where a combination of BookingID and CurrencyID only exist in either Transfers or Extras and you still want to include them in the result (rather than finding the intersect) this query will do that:

如果您想要覆盖的情况是，将BookingID和CurrencyID的组合只存在于传输或附加组件中，并且您仍然希望将它们包含在结果中(而不是找到交集)，那么该查询将会这样做:

SELECT IDs.BookingId, IDs.CurrencyID, e.TotalExtrasPrice,t.TotalTransfersPrice 
FROM (
  SELECT BookingId,CurrencyId FROM transfers 
  UNION
  SELECT BookingId,CurrencyId FROM extras 
) IDs 
LEFT JOIN transfers t ON IDs.BookingId=t.BookingId AND IDs.CurrencyID=t.CurrencyID
LEFT JOIN extras e ON IDs.BookingId=e.BookingId AND IDs.CurrencyID=e.CurrencyID

This query will produce a result identical to your example.

此查询将生成与您的示例相同的结果。

#5

This works. All you need is a simple full outer join.

这个作品。你所需要的只是一个简单的完整的外部连接。

SELECT "BookingID", "CurrencyID",
       ext."TotalExtrasPrice", trans."TotalTransfersPrice"
FROM Transfers trans FULL OUTER JOIN Extras ext
USING ("BookingID", "CurrencyID");

SQLFiddle demo using Oracle.

SQLFiddle演示使用Oracle。

#1