如何在access中创建子查询

laawzig2  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(387)

我对如何正确地执行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;

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






kyvafyod

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。

相关问题