MS Access替代SQL函数ROW_NUMBER() [英] MS Access alternative to SQL function ROW_NUMBER()

问题描述

我与MS Access 07合作，需要一些帮助.

I work with MS Access 07 and need a little help.

我有两个表TbProjectTeam和TbProjectList.我需要比较员工离职日期和项目开始日期.我使用了SQL语法:

I have two tables TbProjectTeam and TbProjectList. I need to compare date of employee turn-out and date of project start. I used that SQL syntax:

SELECT [TbProjectTeam ].[Surname, name] 
FROM TbProjectTeam 
INNER JOIN TbProjectList
ON TbProjectTeam .[DateofTurnOut] <= TbProjectList.[DateOfStart] 
WHERE TbProjectList.[ID] = 1 
ORDER BY [Surname, name];

我的目标是将TbSeznamUkolu.[ID] = 1表达式替换为SQL中的ROW_NUMBER() OVER. MS Access不支持此功能，但是不幸的是，我需要了解所有项目的行索引.我想象将显示与每一行匹配的员工.

My aim is to replace 1 in TbSeznamUkolu.[ID] = 1 expression with something as ROW_NUMBER() OVER in SQL. MS Access doesn't support this function but unfortunately I need to know row index of all projects. I imagine that will be displayed matching employees for every row.

有人可以帮我吗?非常感谢.

Can anyone help me please? Big thanks.

推荐答案

MS Access具有 AutoNumber 数据类型，该数据类型设置记录上的序号.您的数据集将需要包含 AutoNumber 字段，以表示T-SQL中的ROW.由于自动编号是数字，因此您可以在其上执行>和<功能. 唯一的问题是记录在添加到表中时将按顺序编号.如果可以控制添加它们的顺序，那么没有问题，但是如果要将 AutoNumber 字段添加到现有的填充表中，则该顺序可能不符合您的要求.

MS Access has an AutoNumber data type that sets sequential numbers on records. Your data set would need to include an AutoNumber field to represent the ROW in T-SQL. Since the AutoNumber is numeric you could do > and < functions on it. The only problem with this is that the records will be numbered sequentially as they are added to the table. If you can control the sequence in which they are added then there is no problem, but if you were to add the AutoNumber field to an existing populated table, the sequence may not match your requirements.

我意识到这个问题有点老了，但我希望这会有所帮助.

I realise this question is a bit old but I hope this helps.

这篇关于MS Access替代SQL函数ROW_NUMBER()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！