在MS Access中达到ROW_NUMBER/PARTITION BY [英] Achieving ROW_NUMBER / PARTITION BY in MS Access

问题描述

如何通过MS访问来实现分区上的行号.我在Google上找不到信息，请问该怎么做

How to achieve the Row number over partition by in the MS access .I Google but cant find information can you please how to do this

RowNumber Over(Partition by city Order By EmployeeID)

我的数据看起来像这样

DOC_TYPE    Ino
3a  1800xxc1
3b  1810xxc2
3c  1700xxc3
3a  1700xxc4
3a  1800xxc5
3a  1800xxc6
3b  1800xxc7

我需要这样

DOC_TYPE    Ino Seq
3a  1800xxc1    1
3a  1700xxc4    2
3a  1800xxc5    3
3a  1800xxc6    4
3b  1810xxc2    1
3b  1800xxc7    2
3c  1700xxc3    1

这是我的查询

SELECT t1.RT_TAXCODE, t1.INV_NO, COUNT(*) AS Sno
FROM GroupByTAXCODE AS t1 INNER JOIN GroupByTAXCODE AS t2 ON (t2.RT_TAXCODE = t1.RT_TAXCODE) AND (t2.Inv_no <= t1.Inv_no)
GROUP BY t1.RT_TAXCODE, t1.INV_NO
HAVING COUNT(*)=1
ORDER BY 1, 3;

这花费了更多的时间，例如30秒

This is taking more time as f 30 seconds

推荐答案

在许多情况下，我们可以通过在表上执行不相等的 self-join 并将结果汇​​总来获得相似的结果.例如，对于名为[MyData]

In many cases we can achieve a similar result by performing an unequal self-join on the table and aggregating the results. For example, for data in a table named [MyData]

Ino  TYPE      DOC
---  --------  ---
  1  1800xxc1  3a 
  2  1810xxc2  3b 
  3  1700xxc3  3c 
  4  1700xxc4  3a 
  5  1800xxc5  3a 
  6  1800xxc6  3a 
  7  1800xxc7  3b 

查询

SELECT 
    t1.DOC,
    t1.TYPE,
    COUNT(*) AS [Ino Seq]
FROM 
    MyData AS t1
    INNER JOIN
    MyData AS t2
        ON t2.DOC = t1.DOC
            AND t2.Ino <= t1.Ino
GROUP BY
    t1.DOC,
    t1.TYPE
ORDER BY 1, 3

返回

DOC  TYPE      Ino Seq
---  --------  -------
3a   1800xxc1        1
3a   1700xxc4        2
3a   1800xxc5        3
3a   1800xxc6        4
3b   1810xxc2        1
3b   1800xxc7        2
3c   1700xxc3        1

这篇关于在MS Access中达到ROW_NUMBER/PARTITION BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！