如何在access中创建子查询

laawzig2 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(350)

我对如何正确地执行microsoftaccess子查询感到困惑。我有一个任务，我应该在其中创造一个，但我不能为我的生命想出它。问题是：
问题3。获取订购书籍的所有第二作者的姓和名。把这些名字印在书名旁边。同时显示每一个标题的总销售量。总销售金额是订单行中的图书数量乘以它们的价格，再加上同一作者的所有订单。按销售总额的降序排列结果（提示：使用sum函数添加乘法，并按作者和标题分组）。
我已经计算出了这两部分的sql（或者我认为是这样）。以下是我目前掌握的情况：
打印标题、姓氏和名：

SELECT Book.Title, Author.LastName, Author.FirstName<br>
FROM Wrote, Author, Book, OrderLine<br>
WHERE Wrote.AuthorRank = 2<br>
AND Wrote.AuthorId = Author.AuthorID<br>
AND Book.ISBN = Wrote.ISBN<br>
AND OrderLine.ISBN = Book.ISBN;

要计算销售总额：

SELECT SUM(Quantity * Price) AS TotalAmountSold<br>
FROM Book, OrderLine<br>
GROUP BY Title;

我正在努力找出如何把这些结合起来，以达到问题的要求。有什么小贴士能帮我理解怎么做这样的事吗？提前谢谢。

sql subquery ms-access

来源：https://stackoverflow.com/questions/62828227/how-to-create-a-subquery-in-access

1条答案

按热度按时间

kyvafyod1#

生成并保存查询1:

SELECT OrderLine.ISBN, Title, Sum([Quantity]*[Price]) AS TotalAmountSold
FROM OrderLine INNER JOIN Book ON OrderLine.ISBN = Book.ISBN
GROUP BY OrderLine.ISBN, Title;

生成查询2:

SELECT Author.AuthorID, FirstName, LastName, Title, TotalAmountSold
FROM (Author INNER JOIN Wrote ON Author.AuthorID = Wrote.AuthorID) 
INNER JOIN Query1 ON Wrote.ISBN = Query1.ISBN
WHERE (((Wrote.AuthorRank)=2))
ORDER BY TotalAmountSold DESC;

如果您想要一个多功能sql，请将query1 sql复制/粘贴到query2中，结果如下：

SELECT Author.AuthorID, FirstName, LastName, Title, TotalAmountSold
FROM (Author INNER JOIN Wrote ON Author.AuthorID = Wrote.AuthorID) 
INNER JOIN 
     (SELECT OrderLine.ISBN, Title, Sum([Quantity]*[Price]) AS TotalAmountSold
      FROM OrderLine INNER JOIN Book ON OrderLine.ISBN = Book.ISBN
      GROUP BY OrderLine.ISBN, Title) AS Query1 
ON Wrote.ISBN = Query1.ISBN
WHERE (((Wrote.AuthorRank)=2))
ORDER BY TotalAmountSold DESC;

保存查询2并删除查询1。

赞(0）回复(0）举报 2021-07-24

我来回答

如何在access中创建子查询

1条答案

相关问题

热门标签

最新问答