在Access SQL中创建row_num列，首先按字段排序

I am trying to write a simple query that will take a table, order by the date field of that table and add a column that includes a row count. This is the easiest thing in T-SQL, but Access does not support the Row_Num() function.

我正在尝试编写一个简单的查询，它将采用一个表，按该表的日期字段排序，并添加一个包含行计数的列。这是T-SQL中最简单的事情，但Access不支持Row_Num（）函数。

So, let's say my "Dates" table looks like this:

所以，让我们说我的“日期”表看起来像这样：

ID       Date
1        02/01/2017
2        02/03/2017
3        01/27/2017
4        02/05/2017
5        02/01/2017
6        02/03/2017

And the result of my Access query should look like this:

我的Access查询的结果应如下所示：

ID       Date          RowNum
3        01/27/2017    1
1        02/01/2017    2
5        02/01/2017    3
2        02/03/2017    4
6        02/03/2017    5
4        02/05/2017    6

I have tried to find an answer to this question, but all the answers I have found seem to rely on the difference in the values of the ID field from one row to the next. So then I tried to apply the concepts I found (creating a column with a dcount where A.ID > ID) to the Date field, but then I get a count per date. But I need a count for every single date, even if there might be multiple dates that are the same.

我试图找到这个问题的答案，但我发现的所有答案似乎都依赖于从一行到下一行的ID字段值的差异。那么我试着将我找到的概念（创建一个带有dcID的列，其中A.ID> ID）应用到Date字段，但随后我得到了每个日期的计数。但是我需要计算每个日期，即使可能有多个日期是相同的。

Thanks in advance

提前致谢

1 个解决方案

#1

One method is a correlated subquery:

一种方法是相关子查询：

select d.*,
       (select count(*) from dates as d2 where d2.date <= d.date) as rownum
from dates as d
order by d.date;

This is not very efficient, but on a small table it does accomplish what you want. The simplest way, though, is probably to use a cursor over the table.

这不是很有效，但在一张小桌子上它确实能达到你想要的效果。但最简单的方法可能是在表格上使用光标。

This assumes that the dates are distinct, as in the example data in the question.

这假定日期是不同的，如问题中的示例数据。

EDIT:

编辑：

On closer inspection, the dates are not unique. So you can use multiple conditions:

仔细观察，日期并不是唯一的。所以你可以使用多个条件：

select d.*,
       (select count(*)
        from dates as d2
        where d2.date < d.date or
              (d2.date = d.date and d2.id <= d.id)
       ) as rownum
from dates as d
order by d.date;

#1