如何在SQL Access数据库表中查找第11个条目?
问题描述:
我如何在Access数据库表中找到第11个条目?我不得不使用顶部功能,而我目前的查询看起来像这样:如何在SQL Access数据库表中查找第11个条目?
SELECT TOP 11 Passenger.Name, SUM(Manifest.Ticketprice) AS [Total Amount Spent on Tickets]
FROM Passenger INNER JOIN Manifest ON Passenger.PassengerNumber=Manifest.PassengerNumber
GROUP BY Passenger.Name
ORDER BY SUM(Manifest.Ticketprice) DESC;
答
SELECT TOP 11 Passenger.Name, SUM(Manifest.Ticketprice) AS [Total Amount Spent on Tickets]
FROM Passenger INNER JOIN Manifest ON Passenger.PassengerNumber=Manifest.PassengerNumber
GROUP BY Passenger.Name
ORDER BY SUM(Manifest.Ticketprice) DESC LIMIT 1;
答
您可以使用子查询:
SELECT TOP 1 T.Name, T.[Total Amount Spent on Tickets]
FROM
(SELECT TOP 11 Passenger.Name, SUM(Manifest.Ticketprice) AS [Total Amount Spent on Tickets]
FROM Passenger INNER JOIN Manifest ON Passenger.PassengerNumber=Manifest.PassengerNumber
GROUP BY Passenger.Name
ORDER BY SUM(Manifest.Ticketprice) DESC) As T
ORDER BY T.[Total Amount Spent on Tickets] ASC
有一个在访问没有“限制”关键字。我相信答案是使用嵌套的“TOP”查询: SELECT TOP 1 * FROM (SELECT TOP 11 Passenger.Name,SUM(Manifest.Ticketprice)AS [门票上的总金额] FROM Passenger INNER JOIN Manifest ON Passenger.PassengerNumber = Manifest.PassengerNumber GROUP BY Passenger.Name ORDER BY SUM(Manifest.Ticketprice)DESC); –