每组访问查询计数器 [英] Access query counter per group

问题描述

是否可以在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

这篇关于每组访问查询计数器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！