在oracle中实现分页和分组统计_orcale分组和排序分页混合使用

我这里用的是oracle10，oracle11中有一个自增分区的。

1 首先来说一下需求：

我要实现的是：按照时间字段和功能字段进行分组，接着在分组的基础上进行排序后，选择若干记录作为我所需要的结果集，即分页的实现。
不知道大家有没有听懂，没听懂的就是我表述的有问题，大家见谅。

2 接着创建一个id自增的数据表

这里也实现了一个按照时间来进行表分区的分区表。表分区和分区表不是一个意思，表分区是一种思想，分区表是表分区的一种实现方式。

create table OFE_MENU_USER
(
  menu_id       INTEGER not null,
  username      VARCHAR2(31),
  parent_menu   VARCHAR2(31),
  sub_operation VARCHAR2(50),
  click_count   NUMBER,
  createtime    DATE
)
partition by range (CREATETIME)
(
  partition OMU_20181101 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace OFE
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
    partition OMU_20181201 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace OFE
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
 )
--- 在创建了数据表后，再来创建自增id，自增id需要用到序列和触发器
DROP SEQUENCE ofe_menu_user_seq;---第一次执行可忽略此句
CREATE SEQUENCE ofe_menu_user_seq MINVALUE 1 MAXVALUE 99999999
INCREMENT BY 1
START WITH 1;

CREATE OR REPLACE TRIGGER ofe_menu_user_tb_tri
BEFORE INSERT ON ofe_menu_user
FOR EACH ROW
  BEGIN
    SELECT ofe_menu_user_seq.nextval INTO :new.menu_id FROM dual;
  END;

--- 测试插入操作
INSERT INTO OFE_MENU_USER(username,parent_menu,Sub_Operation,Click_Count,createtime)
VALUES('TEST01','父菜单','子菜单',1,to_date('2018/11/16','yyyy/MM/dd'));
commit;
select * ofe_menu_user;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

3 现在就进入主题

首先来实现按照时间和用户点击操作名称进行分组

SELECT TO_CHAR(createtime,'YYYY/MM/DD') TIME,
		SUM(click_count) sumCount,
		sub_operation 
FROM OFE_MENU_USER
WHERE 
	TO_CHAR(createtime,'YYYY') = TO_CHAR(SYSDATE,'YYYY') 
GROUP BY TO_CHAR(createtime,'YYYY/MM/DD'),sub_operation
ORDER BY SUM(click_count) DESC
1
2
3
4
5
6
7
8

上面的sql语句中，是按照天来进行分组的，还有按照月，年来进行分组的，相应的只需要改变 to_char()部分的内容即可；
若还想要实现按照用户点击名称分组，则只需在group by后面再加上你要的分组名字。

再此基础上，我还想要某一段时间的数据，这时可以使用between and语句来实现。或者是>= 和 <= 一起是用来实现。
语句如下：

SELECT TO_CHAR(createtime,'YYYY/MM/DD') TIME,
		SUM(click_count) sumCount,
		sub_operation 
FROM OFE_MENU_USER
WHERE 
	TO_CHAR(createtime,'YYYY') = TO_CHAR(SYSDATE,'YYYY') 
	AND createtime >= to_date('2018/11/10','yyyy/MM/dd')
    	AND createtime <= to_date('2018/11/19','yyyy/MM/dd')
GROUP BY TO_CHAR(createtime,'YYYY/MM/DD'),sub_operation
ORDER BY SUM(click_count) DESC
1
2
3
4
5
6
7
8
9
10

4 分页

在上面的功能上来继续完善，上面完成了分组，这里就来实现分页。
分页语句如下，也是这次笔记的完整语句：

SELECT 
	sc.time AS xTime,
	sc.sumCount AS xCount,
	sc.sub_operation AS subOperation 
FROM
  	(SELECT 
  		oc.*,ROWNUM rn 
  	FROM
    		(SELECT 
    			TO_CHAR(createtime,'YYYY/MM/DD') TIME,
    			SUM(click_count) sumCount,
    			sub_operation 
    		FROM 
    			OFE_MENU_USER
    		WHERE 
    			TO_CHAR(createtime,'YYYY') = TO_CHAR(SYSDATE,'YYYY') 
          		AND createtime >= to_date('2018/11/10','yyyy/MM/dd') 
          		AND createtime <= to_date('2018/11/19','yyyy/MM/dd')
    		GROUP BY 
    			TO_CHAR(createtime,'YYYY/MM/DD'),
    			sub_operation,parent_menu
    		ORDER BY SUM(click_count) DESC) oc
   	) sc
WHERE rn >=5 AND rn <=10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

好了，这是典型的oracle分页语句，三层嵌套。。。
注意，在第二层嵌套中一定要给ROWNUM起一个别名，这是用于最外层当中行数的获取的条件来的。

好了，如果看不懂的，还是我的问题，大家可以复制黏贴去看看，测试一下，就可以看到效果了。