在 SQL 中加入 5 个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14803135/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining 5 tables in SQL
提问by czy
I'm having trouble with an SQL statement.
我在使用 SQL 语句时遇到问题。
Basically I have 5 tables, these are:
基本上我有5张桌子，它们是：
Books, Records, OrderedBooks, OrderedRecords and Orders.
Books、Records、OrderedBooks、OrderedRecords 和 Orders。
My orders contain all the orders customers place, My orderedRecords, and orderedBooks contain all the books and records which are related to a customers order and obviously the books and records tables contain the information relating to each book and record.
我的订单包含客户下的所有订单，MyorderedRecords 和orderedBooks 包含与客户订单相关的所有账簿和记录，显然账簿和记录表包含与每个账簿和记录相关的信息。
My orderedrecords and orderedbooks contain a BookID and RecordID which relate to the corresponding books / records.
我的orderedrecords 和orderedbooks 包含与相应的书籍/记录相关的BookID 和RecordID。
I'm writing an app which needs to show booktitle, bookcost, bookQuantity, booksTotal(price*quantity) recordtitle, recordcost, recordQuantity, recordsTotal.
我正在编写一个需要显示书名、书本成本、书本数量、书本总（价格*数量）记录标题、记录成本、记录数量、记录总数的应用程序。
The total for books and records does not have to be calculated using mathematical functions in the SQL.
账簿和记录的总数不必使用 SQL 中的数学函数计算。
So far I've managed to get it to give me exactly what I want, however, say I have 3 book orders, and 1 record order, for the remaining two rows of the record order, it will duplicate the data instead of simply being empty. I've tried Left and Right joins which have not worked.
到目前为止，我已经设法让它给我我想要的东西，但是，假设我有 3 个图书订单和 1 个记录订单，对于记录订单的其余两行，它将复制数据而不是简单地空的。我尝试过左连接和右连接，但都没有用。
Here is my SQL statement...
这是我的 SQL 语句...
SELECT Books.Title AS BookTitle
, Books.Cost AS BookCost
, OrderedBooks.Quantity AS BookQuantity
, OrderedBooks.Total AS BooksTotal
, Records.Title AS RecordsTitle
, Records.Cost AS RecordsCost
, OrderedRecords.Quantity AS RecordQuantity
, OrderedRecords.Total AS RecordsTotal
-- Nothing wrong with select part
FROM Orders
INNER JOIN OrderedBooks ON OrderedBooks.OrderID = Orders.ID
FULL OUTER JOIN OrderedRecords ON OrderedRecords.OrderID = Orders.ID
LEFT OUTER JOIN Books ON OrderedBooks.BookID = Books.ID
LEFT OUTER JOIN Records ON OrderedRecords.RecordID = Records.ID
WHERE (Orders.ID = 9)
It returns the correct ordered books and records for the correct order number, however I cannot seem to get rid of the duplicated rows
它返回正确订单号的正确订购书籍和记录，但是我似乎无法摆脱重复的行
回答by czy
Books.id:
图书编号：
SELECT Books.Title AS BookTitle
, Books.Cost AS BookCost
, OrderedBooks.Quantity AS BookQuantity
, OrderedBooks.Total AS BooksTotal
, Records.Title AS RecordsTitle
, Records.Cost AS RecordsCost
, OrderedRecords.Quantity AS RecordQuantity
, OrderedRecords.Total AS RecordsTotal
FROM Orders
INNER JOIN OrderedBooks ON OrderedBooks.OrderID = Orders.ID
FULL OUTER JOIN OrderedRecords ON OrderedRecords.OrderID = Orders.ID
LEFT OUTER JOIN Books ON OrderedBooks.BookID = Books.ID
LEFT OUTER JOIN Records ON OrderedRecords.RecordID = Records.ID
WHERE (Orders.ID = 9)
GROUP BY Books.ID
回答by Paul Grimshaw
How about a simple SELECT DISTINCT:
一个简单的 SELECT DISTINCT 怎么样：
SELECT DISTINCT
Books.Title AS BookTitle
, Books.Cost AS BookCost
, OrderedBooks.Quantity AS BookQuantity
, OrderedBooks.Total AS BooksTotal
, Records.Title AS RecordsTitle
, Records.Cost AS RecordsCost
, OrderedRecords.Quantity AS RecordQuantity
, OrderedRecords.Total AS RecordsTotal
-- Nothing wrong with select part
FROM Orders
INNER JOIN OrderedBooks ON OrderedBooks.OrderID = Orders.ID
FULL OUTER JOIN OrderedRecords ON OrderedRecords.OrderID = Orders.ID
LEFT OUTER JOIN Books ON OrderedBooks.BookID = Books.ID
LEFT OUTER JOIN Records ON OrderedRecords.RecordID = Records.ID
WHERE (Orders.ID = 9)
回答by Terje D.
As there is no relation between the books and the records belonging to one order, you should not join them together, but instead use two distinct queries to find the information on books and records.
由于属于一个订单的账簿和记录之间没有关系，因此您不应将它们连接在一起，而是使用两个不同的查询来查找有关账簿和记录的信息。
One possibility is then:
一种可能性是：
SELECT 'Book' AS Type
, Books.Title AS Title
, Books.Cost AS Cost
, OrderedBooks.Quantity AS Quantity
, OrderedBooks.Total AS Total
FROM Orders
INNER JOIN OrderedBooks ON OrderedBooks.OrderID = Orders.ID
INNER JOIN Books ON OrderedBooks.BookID = Books.ID
WHERE (Orders.ID = 9)
UNION
SELECT 'Record' AS Type
, Records.Title AS Title
, Records.Cost AS Cost
, OrderedRecords.Quantity AS Quantity
, OrderedRecords.Total AS Total
FROM Orders
INNER JOIN OrderedRecords ON OrderedRecords.OrderID = Orders.ID
INNER JOIN Records ON OrderedRecords.RecordID = Records.ID
WHERE (Orders.ID = 9)
回答by michelle.ann.diaz
Try to use GROUP BY statement
尝试使用 GROUP BY 语句
Like this:
像这样：
SELECT Books.Title AS BookTitle
, Books.Cost AS BookCost
, OrderedBooks.Quantity AS BookQuantity
, OrderedBooks.Total AS BooksTotal
, Records.Title AS RecordsTitle
, Records.Cost AS RecordsCost
, OrderedRecords.Quantity AS RecordQuantity
, OrderedRecords.Total AS RecordsTotal
FROM Orders
INNER JOIN OrderedBooks ON OrderedBooks.OrderID = Orders.ID
FULL OUTER JOIN OrderedRecords ON OrderedRecords.OrderID = Orders.ID
LEFT OUTER JOIN Books ON OrderedBooks.BookID = Books.ID
LEFT OUTER JOIN Records ON OrderedRecords.RecordID = Records.ID
WHERE (Orders.ID = 9)
GROUP BY BookTitle
