每组访问查询计数器

本文介绍了每组访问查询计数器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

是否可以在Access查询中添加按组排序(排序/组)字段，以使排序依据值如下表所示：

注意：ID是唯一索引(自动编号)

推荐答案

在实际的RDBMS上，通常使用窗口函数ROW_NUMBER来实现此目的。

select * , row_number() over (partition by Group1 order by Value, ID) as Rownum from yourtable

但另一种方法是使用相关子查询。

select *, (select count(*) from yourtable t2 where t2.Group1 = t.Group1 and (t2.Value < t.Value or (t2.Value = t.Value and t2.ID <= t.ID)) ) as Rownum from yourtable t

额外：

显示ROW_NUMBER、RANK和DENSE_RANK之间差异的简单测试

create table yourtable ( ID int identity(1,1) primary key, Group1 int, Value int ); insert into yourtable (Group1, Value) values (10,100),(10,150),(10,150),(10,150),(10,360) , (200,360),(200,420),(200,420),(200,500),(200,500) -- -- ROW_NUMBER (to get a sequence per group) -- select * , ROW_NUMBER() OVER (PARTITION BY Group1 ORDER BY Value, ID) as Rownum from yourtable order by Group1, Rownum ID | Group1 | Value | Rownum -: | -----: | ----: | -----: 1 | 10 | 100 | 1 2 | 10 | 150 | 2 3 | 10 | 150 | 3 4 | 10 | 150 | 4 5 | 10 | 360 | 5 6 | 200 | 360 | 1 7 | 200 | 420 | 2 8 | 200 | 420 | 3 9 | 200 | 500 | 4 10 | 200 | 500 | 5 -- -- Emulating ROW_NUMBER via a correlated sub-query -- select *, (select count(*) from yourtable t2 where t2.Group1 = t.Group1 and (t2.Value < t.Value or (t2.Value = t.Value and t2.ID <= t.ID)) ) as Rownum from yourtable t order by Group1, Rownum ID | Group1 | Value | Rownum -: | -----: | ----: | -----: 1 | 10 | 100 | 1 2 | 10 | 150 | 2 3 | 10 | 150 | 3 4 | 10 | 150 | 4 5 | 10 | 360 | 5 6 | 200 | 360 | 1 7 | 200 | 420 | 2 8 | 200 | 420 | 3 9 | 200 | 500 | 4 10 | 200 | 500 | 5 -- -- RANK (same values get same rank, but with gaps) -- select * , RANK() over (partition by Group1 order by Value) as Ranknum from yourtable order by Group1, Ranknum ID | Group1 | Value | Ranknum -: | -----: | ----: | ------: 1 | 10 | 100 | 1 2 | 10 | 150 | 2 3 | 10 | 150 | 2 4 | 10 | 150 | 2 5 | 10 | 360 | 5 6 | 200 | 360 | 1 7 | 200 | 420 | 2 8 | 200 | 420 | 2 9 | 200 | 500 | 4 10 | 200 | 500 | 4 -- -- Emulating RANK via a correlated sub-query -- select *, (select count(t2.value)+1 from yourtable t2 where t2.Group1 = t.Group1 and t2.Value < t.Value) as Ranknum from yourtable t order by Group1, Ranknum ID | Group1 | Value | Ranknum -: | -----: | ----: | ------: 1 | 10 | 100 | 1 2 | 10 | 150 | 2 3 | 10 | 150 | 2 4 | 10 | 150 | 2 5 | 10 | 360 | 5 6 | 200 | 360 | 1 7 | 200 | 420 | 2 8 | 200 | 420 | 2 9 | 200 | 500 | 4 10 | 200 | 500 | 4 -- -- DENSE_RANK (same values get same rank, without gaps) -- select * , DENSE_RANK() over (partition by Group1 order by Value) as Ranknum from yourtable order by Group1, Ranknum ID | Group1 | Value | Ranknum -: | -----: | ----: | ------: 1 | 10 | 100 | 1 2 | 10 | 150 | 2 3 | 10 | 150 | 2 4 | 10 | 150 | 2 5 | 10 | 360 | 3 6 | 200 | 360 | 1 7 | 200 | 420 | 2 8 | 200 | 420 | 2 9 | 200 | 500 | 3 10 | 200 | 500 | 3 -- -- Emulating DENSE_RANK via a correlated sub-query -- select *, (select count(distinct t2.Value) from yourtable t2 where t2.Group1 = t.Group1 and t2.Value <= t.Value ) as Ranknum from yourtable t order by Group1, Ranknum ID | Group1 | Value | Ranknum -: | -----: | ----: | ------: 1 | 10 | 100 | 1 2 | 10 | 150 | 2 3 | 10 | 150 | 2 4 | 10 | 150 | 2 5 | 10 | 360 | 3 6 | 200 | 360 | 1 7 | 200 | 420 | 2 8 | 200 | 420 | 2 9 | 200 | 500 | 3 10 | 200 | 500 | 3

db<；>；小提琴here