Access select语句中的Row_Number()

我需要做的是创建一个select语句，为每个pageID/placeID组合提供一个唯一的数字，但是数字必须按照它们在书中出现的顺序排列.在SQL Server中，我可以这样做:SELECT ROW_NUMBER() OVER(ORDER BY bp.bookID, bp.pageOrder, pp.placeID) AS uniqueNumber, pp.pageID, pp.placeIDFROM booksAndPages AS bp INNER JOIN pagesAndPlaces AS pp ON bp.pageID = pp.pageID不幸的是，我无法使用Access.理想情况下，我希望使用一条SQL语句(如果可能的话)执行此操作，类似于上面的语句，但我也将使用VBA尝试它.非常感谢您的帮助.解决方案这是您想要的查询:SELECT ROW_NUMBER() OVER (ORDER BY bp.bookID, bp.pageOrder, pp.placeID) AS uniqueNumber, pp.pageID, pp.placeIDFROM booksAndPages AS bp INNER JOIN pagesAndPlaces AS pp ON bp.pageID = pp.pageID;您可以使用相关子查询获得相同的结果.更复杂，更昂贵，但有可能:SELECT (select count(*) from booksAndPages AS bp2 INNER JOIN pagesAndPlaces AS pp2 ON bp2.pageID = pp2.pageID where bp2.bookID < bp.bookID or (bp2.bookID = bp.bookID and bp2.pageOrder < bp.pageOrder) or (bp2.bookID = bp.bookID and bp2.pageOrder = bp.pageOrder and bp2.placeId <= pp.PlaceId ) ) as uniqueNumber, pp.pageID, pp.placeIDFROM booksAndPages AS bp INNER JOIN pagesAndPlaces AS pp ON bp.pageID = pp.pageID;这假定组合bookId，pageOrder，placeId`是唯一的.I believe similar questions have been asked but I can't quite find a solution that works for me.I've got a database that I use to sort through digitised books and their pages and I'm trying to sort through several thousand pages that contain maps. Of the two tables I'm using the first lists all the pages in a book and the order they occur in the book, it's got three columns (bookID, pageOrder, pageID), each page has its own row. The second table lists all the places (in a map) that occur on each page, it has two columns (pageID, placeID) if there are multiple places on one page then a new row is added to the table for each place.What I need to do is create a select statement that gives every pageID/placeID combination a unique number but the numbers must go in the order they appear in the book. In SQL Server I would do this:SELECT ROW_NUMBER() OVER(ORDER BY bp.bookID, bp.pageOrder, pp.placeID) AS uniqueNumber, pp.pageID, pp.placeIDFROM booksAndPages AS bp INNER JOIN pagesAndPlaces AS pp ON bp.pageID = pp.pageIDUnfortunately, I'm stuck using Access. Ideally I'd like to do it (if possible) with a single SQL statement, similar to the one above but I would also try it using VBA.Any help is greatly appreciated. 解决方案 This is the query that you want:SELECT ROW_NUMBER() OVER (ORDER BY bp.bookID, bp.pageOrder, pp.placeID) AS uniqueNumber, pp.pageID, pp.placeIDFROM booksAndPages AS bp INNER JOIN pagesAndPlaces AS pp ON bp.pageID = pp.pageID;You can get the same result using a correlated subquery. More complicated and more expensive, but possible:SELECT (select count(*) from booksAndPages AS bp2 INNER JOIN pagesAndPlaces AS pp2 ON bp2.pageID = pp2.pageID where bp2.bookID < bp.bookID or (bp2.bookID = bp.bookID and bp2.pageOrder < bp.pageOrder) or (bp2.bookID = bp.bookID and bp2.pageOrder = bp.pageOrder and bp2.placeId <= pp.PlaceId ) ) as uniqueNumber, pp.pageID, pp.placeIDFROM booksAndPages AS bp INNER JOIN pagesAndPlaces AS pp ON bp.pageID = pp.pageID;This assumes that the combination bookId, pageOrder, placeId` is unique. 这篇关于Access select语句中的Row_Number()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！