Access select语句中的Row

本文介绍了Access select语句中的Row_Number()的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我相信有人问过类似的问题，但我找不到适合我的解决方案.

我有一个数据库，可以用来对数字化书籍及其页面进行排序，而我正试图对包含地图的数千页进行排序.在我使用的两个表中，第一个列出了书中的所有页面以及它们在书中出现的顺序，它有三列(bookID，pageOrder，pageID)，每个页面都有自己的行.第二张表列出了每个页面上出现的所有位置(在地图中)，如果一个页面上有多个位置，则它具有两列(pageID，placeID)，然后为每个位置在表中添加新行.

我需要做的是创建一个select语句，为每个pageID/placeID组合提供一个唯一的数字，但是数字必须按照它们在书中出现的顺序排列.在SQL Server中，我可以这样做:

SELECT ROW_NUMBER() OVER(ORDER BY bp.bookID, bp.pageOrder, pp.placeID) AS uniqueNumber, pp.pageID, pp.placeID FROM 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.placeID FROM 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.placeID FROM 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.placeID FROM booksAndPages AS bp INNER JOIN pagesAndPlaces AS pp ON bp.pageID = pp.pageID

Unfortunately, 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.placeID FROM 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.placeID FROM booksAndPages AS bp INNER JOIN pagesAndPlaces AS pp ON bp.pageID = pp.pageID;

This assumes that the combination bookId, pageOrder, placeId` is unique.