问题描述
如何利用行数超过分区依据"?在 MS 访问中?我用谷歌搜索了它，但找不到任何关于如何去做的信息.我说的是这个特定的函数，它为结果集的分区内的每一行分配一个连续整数:
How can you make use of "Row number over partition by" in MS access? I googled it, but couldn't find any information as to how one would go about doing this. I'm talking about this particular function, which assigns a sequential integer to each row within the partition of a result set:
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
但是，我需要它看起来像这样:
However, I need it to look like this:
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;
这需要更多时间作为 f 30 秒
This is taking more time as f 30 seconds
推荐答案
在很多情况下，我们可以通过对表执行不相等的自连接并聚合结果来获得类似的结果.例如，对于名为 [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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！