访问查询在 T-SQL 中产生类似 ROW_NUMBER() 的结果 [英] Access query producing results like ROW_NUMBER() in T-SQL

问题描述

我们在 MS Access 中有 ROW_NUMBER 函数吗?如果它有那么请让我知道它的任何语法，因为我被困在这里.我试过论坛，但我得到了 sql server 语法.以下是我的查询:

Do we have ROW_NUMBER function in MS Access? If it has then please let me know any syntax for it as I am stuck here. I have tried forums but I get sql server syntax. Following is my query:

select 
    ROW_NUMBER() OVER (ORDER BY t.TID) AS uni , 
    t.TSource as [Source],
    t.TText as [Text],
    u.Name as [UserId],
    u.Image_Url as [ImageFilePath], 
from table1 t inner join table2 u on t.UserId = u.UIds

但它给出了语法错误.

推荐答案

在 Access SQL 中，我们有时可以使用 self-join 来生成排名顺序.例如，对于 [table1]

In Access SQL we can sometimes use a self-join to produce a rank order. For example, for [table1]

TID  UserId  TSource  TText
---  ------  -------  -----
412  homer   foo      bar  
503  marge   baz      thing
777  lisa    more     stuff

查询

SELECT 
    t1a.TID, 
    t1a.UserId, 
    t1a.TSource, 
    t1a.TText,
    COUNT(*) AS TRank
FROM
    table1 AS t1a
    INNER JOIN
    table1 AS t1b
        ON t1a.TID >= t1b.TID
GROUP BY
    t1a.TID, 
    t1a.UserId, 
    t1a.TSource, 
    t1a.TText

生产

TID  UserId  TSource  TText  TRank
---  ------  -------  -----  -----
412  homer   foo      bar        1
503  marge   baz      thing      2
777  lisa    more     stuff      3

并且我们可以将其用作 JOIN 到另一个表的子查询

and we can use that as a subquery in our JOIN to the other table

select 
    t.TRank as uni,
    t.TSource as [Source],
    t.TText as [Text],
    u.Name as [UserId],
    u.Image_Url as [ImageFilePath]
from 
    (
        SELECT 
            t1a.TID, 
            t1a.UserId, 
            t1a.TSource, 
            t1a.TText,
            COUNT(*) AS TRank
        FROM
            table1 AS t1a
            INNER JOIN
            table1 AS t1b
                ON t1a.TID >= t1b.TID
        GROUP BY
            t1a.TID, 
            t1a.UserId, 
            t1a.TSource, 
            t1a.TText
    ) AS t 
    INNER JOIN 
    table2 AS u 
        ON  t.UserId = u.UIds

产生类似的东西

uni  Source  Text   UserId        ImageFilePath
---  ------  -----  ------------  -------------
  1  foo     bar    HomerSimpson  whatever1    
  2  baz     thing  MargeSimpson  whatever2    
  3  more    stuff  LisaSimpson   whatever3    

这篇关于访问查询在 T-SQL 中产生类似 ROW_NUMBER() 的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！