access查询生成序列号
(2018-11-30 15:08:48)
标签:
access模拟sqlrownumber |
分类: 代码天地 |
access 查询生成序列号
access 没有 sql中的
rownumber,若要排序一些有重复数值的字段记录,可以使用DCount和Count产生序号
Count是聚合函数,使用它其它查询的字段也需要使用聚合,比较麻烦,此时DCount就有优势了。
一
各期times各班class参加attend活动的人数,建立表T1
ID times class attend
1, 1, 1, 10
2, 1, 2, 15
3, 2, 1, 19
4, 2, 2, 15
5, 3, 1, 8
6, 3, 2, 9
7, 4, 1, 10
8, 4, 2, 14
二 查看1班各期情况,查询为
Q1
SELECT times, class, attend FROM t1 WHERE class=1 ;
times class attend
1, 1, 10
2, 1, 19
3, 1, 8
4, 1, 10
三 按参加人数多少,增加一个序号列 no,查询为 Q2
SELECT q1.times, q1.class, q1.attend,
DCount("attend","q1","attend>=" & [q1].[attend]) AS no
FROM q1
times class attend no
1, 1, 10, 3
2, 1, 19, 1
3, 1, 8, 4
4, 1, 10, 3
SELECT q1.id,q1.times, q1.class, q1.attend,
(DCount("attend","q1","attend=" & [q1].[attend] & " and id
>" & [q1].[id])+DCount("attend","q1","attend>" &
[q1].[attend])) AS no
FROM q1
id times class attend no
1, 1, 1, 10, 2
3, 2, 1, 19, 0
5, 3, 1, 8, 3
7, 4, 1, 10, 1
SELECT q1.id,q1.times, q1.class, q1.attend,
(DCount("attend","q1","attend=" & [q1].[attend] & " and id
>" & [q1].[id])+DCount("attend","q1","attend>" &
[q1].[attend]))+1 AS no
FROM q1
id times class attend no
1, 1, 1, 10, 3
3, 2, 1, 19, 1
5, 3, 1, 8, 4
7, 4, 1, 10, 2
四 通过上面的分析, 按no降序排序
order by attend desc; 形成查询 Q3
SELECT q1.id,q1.times, q1.class, q1.attend,
(DCount("attend","q1","attend=" & [q1].[attend] & " and id
>" & [q1].[id])+DCount("attend","q1","attend>" &
[q1].[attend]))+1 AS no
FROM q1 ORDER BY attend DESC;
id times class attend no
3, 2, 1, 19, 1
7, 4, 1, 10, 2
1, 1, 1, 10, 3
5, 3, 1, 8, 4
五 最终的目的是,通过序号的唯一,来分页显示庞大的数据
1 比如 每页2条记录,显示第二页的
SELECT top 2 q3.times, q3.class, q3.attend, q3.no
FROM q3
WHERE q3.no>2
ORDER BY q3.no asc;
times class attend no
1, 1, 10, 3
3, 1, 8, 4
2 继续简化,只要必须的字段
SELECT top 2 q3.times, q3.attend
FROM q3
WHERE q3.no>2
ORDER BY q3.no asc;
times attend
1, 10
3, 8
3 通用语句 每页记录个数
page_size=2,当前页号page_num=3,按字段no排序