我在Microsoft Access中有一张表,我想使用Access的选择查询来显示行号,就像在SQL Server中使用ROW_NUMBER()
函数一样。
在SQL Server中,我可以使用以下查询:
SELECT ROW_NUMBER() OVER (ORDER BY tblUser.UserID) AS NoRow, *
FROM tblUser
我在Access中使用相同的查询,但是出现了错误。
你能帮帮我吗?
我在Microsoft Access中有一张表,我想使用Access的选择查询来显示行号,就像在SQL Server中使用ROW_NUMBER()
函数一样。
在SQL Server中,我可以使用以下查询:
SELECT ROW_NUMBER() OVER (ORDER BY tblUser.UserID) AS NoRow, *
FROM tblUser
我在Access中使用相同的查询,但是出现了错误。
你能帮帮我吗?
您可以尝试这个查询:
Select A.*, (select count(*) from Table1 where A.ID>=ID) as RowNo
from Table1 as A
order by A.ID
在MS Access中实现这一点的一种方法是使用子查询,但它并没有类似的功能:
SELECT a.ID,
a.AText,
(SELECT Count(ID)
FROM table1 b WHERE b.ID <= a.ID
AND b.AText Like "*a*") AS RowNo
FROM Table1 AS a
WHERE a.AText Like "*a*"
ORDER BY a.ID;
通过VB函数:
Dim m_RowNr(3) as Variant
'
Function RowNr(ByVal strQName As String, ByVal vUniqValue) As Long
' m_RowNr(3)
' 0 - Nr
' 1 - Query Name
' 2 - last date_time
' 3 - UniqValue
If Not m_RowNr(1) = strQName Then
m_RowNr(0) = 1
m_RowNr(1) = strQName
ElseIf DateDiff("s", m_RowNr(2), Now) > 9 Then
m_RowNr(0) = 1
ElseIf Not m_RowNr(3) = vUniqValue Then
m_RowNr(0) = m_RowNr(0) + 1
End If
m_RowNr(2) = Now
m_RowNr(3) = vUniqValue
RowNr = m_RowNr(0)
End Function
使用方法(无排序选项):
SELECT RowNr('title_of_query_or_any_unique_text',A.id) as Nr,A.*
From table A
Order By A.id
SELECT RowNr('title_of_query_or_any_unique_text',A.id) as Nr,A.*
INTO table_with_Nr
From table A
Order By A.id