在SQL中连接5个表
我在使用SQL语句时遇到了问题。
基本上我有5个表，它们是：
书籍，唱片，OrderedBooks，OrderedRecords和订单。
My orders包含客户下的所有订单，My orderedRecords和orderedBooks包含与客户订单相关的所有帐簿和记录，显然帐簿和记录表包含与每本书和记录相关的信息。
My orderedrecords和orderedbooks包含与相应的书/记录相关的BookID和RecordID。
我正在写一个应用程序，需要显示书名，书本成本，bookQuantity，booksTotal(价格*数量)记录标题，记录成本，recordQuantity，recordsTotal。
不必使用SQL中的数学函数来计算帐簿和记录的总数。
到目前为止，我已经设法给出了我想要的东西，但是，假设我有3个图书订单和1个记录订单，对于记录顺序的其余两行，它将复制数据，而不是简单地为空。我尝试过左连接和右连接，但都不起作用。
下面是我的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)它根据正确的订单号返回正确的有序图书和记录，但是我似乎无法摆脱重复的行
回答 4
Stack Overflow用户
发布于 2014-03-25 17:55:01
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.IDStack Overflow用户
发布于 2013-02-11 06:14:11
简单的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)Stack Overflow用户
发布于 2013-02-11 07:50:43
由于属于一个订单的帐簿和记录之间没有关系，因此您不应该将它们连接在一起，而是使用两个不同的查询来查找有关帐簿和记录的信息。
一种可能性是：
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)https://stackoverflow.com/questions/14803135
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号