Access 选择语句中的 Row_Number() [英] Row_Number() in Access select statement

问题描述

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

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

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

SELECT ROW_NUMBER() OVER(ORDER BY bp.bookID, bp.pageOrder, pp.placeID) AS uniqueNumber, pp.pageID, pp.placeID从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从booksAndPages AS bp INNER JOINpagesAndPlaces AS ppON bp.pageID = pp.pageID;

您可以使用相关子查询获得相同的结果.更复杂、更昂贵，但可能:

SELECT (选择计数(*)从booksAndPages AS bp2 INNER JOINpagesAndPlaces AS pp2开启 bp2.pageID = pp2.pageID其中 bp2.bookID 

这里假设 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.

这篇关于Access 选择语句中的 Row_Number()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！