1.首先创建一个表
2.录入数据如下:
3.查询全表查看
[img]http://dl.iteye.com/upload/attachment/234003/fc41b2cd-f7d1-313d-8190-a47a49b8232d.jpg[/img]
4.用t.t_dept, t.t_project进行分组查询
结果如下:
[img]http://dl.iteye.com/upload/attachment/234007/9a1fd344-8777-3826-a29a-a99e978f833e.jpg[/img]
5.用t.t_dept, t.t_project,并使用t.t_dept来做小计
结果如下:
[img]http://dl.iteye.com/upload/attachment/234009/08126e89-d4ed-34f1-828b-546c3802908b.jpg[/img]
6.用t.t_dept, t.t_project,并使用t.t_dept来做小计,并做一次总计
结果如下:
[img]http://dl.iteye.com/upload/attachment/234012/975ed5e6-df65-3035-925d-a4a74467a8f4.jpg[/img]
7.使用grouping(字段)
结果如下:
[img]http://dl.iteye.com/upload/attachment/234040/92a1e78f-4ea1-322e-8dfd-d914bde760fc.jpg[/img]
[img]http://dl.iteye.com/upload/attachment/234078/c13667ff-9b97-3d85-b1db-a8b53b945014.jpg[/img]
注意: 在存储过程中null不会正确执行 需要改成()
如
完!
create table TE
(
ID VARCHAR2(2),
T_CODE VARCHAR2(4),
T_NAME VARCHAR2(4),
T_AMOUNT INTEGER,
T_DEPT VARCHAR2(4),
T_PROJECT VARCHAR2(4),
T_TYPE VARCHAR2(1)
)
2.录入数据如下:
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('1', '1', '1', 10, '总部', '90', '0');
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('2', '2', '2', 20, '总部', '70', '0');
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('3', '3', '3', 30, '分1', '60', '0');
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('4', '4', '4', 40, '分1', '50', '0');
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('5', '5', '5', 50, '分2', '40', '0');
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('6', '6', '6', 60, '分2', '30', '0');
3.查询全表查看
select * from te;
[img]http://dl.iteye.com/upload/attachment/234003/fc41b2cd-f7d1-313d-8190-a47a49b8232d.jpg[/img]
4.用t.t_dept, t.t_project进行分组查询
select t.t_dept, t.t_project, sum(t.t_amount) from te t
group by t.t_dept, t.t_project;
结果如下:
[img]http://dl.iteye.com/upload/attachment/234007/9a1fd344-8777-3826-a29a-a99e978f833e.jpg[/img]
5.用t.t_dept, t.t_project,并使用t.t_dept来做小计
select t.t_dept, t.t_project, sum(t.t_amount) from te t
group by grouping sets ((t.t_dept, t.t_project), t.t_dept);
结果如下:
[img]http://dl.iteye.com/upload/attachment/234009/08126e89-d4ed-34f1-828b-546c3802908b.jpg[/img]
6.用t.t_dept, t.t_project,并使用t.t_dept来做小计,并做一次总计
select t.t_dept, t.t_project, sum(t.t_amount) from te t
group by grouping sets ((t.t_dept, t.t_project), t.t_dept, null);
结果如下:
[img]http://dl.iteye.com/upload/attachment/234012/975ed5e6-df65-3035-925d-a4a74467a8f4.jpg[/img]
7.使用grouping(字段)
select grouping(t.t_dept),t.t_dept, t.t_project, sum(t.t_amount) from te t
group by grouping sets ((t.t_dept, t.t_project), t.t_dept, null);
结果如下:
[img]http://dl.iteye.com/upload/attachment/234040/92a1e78f-4ea1-322e-8dfd-d914bde760fc.jpg[/img]
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('1', '1', '1', 10, '总部', '90', '1');
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('2', '2', '2', 20, '总部', '70', '1');
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('3', '3', '3', 30, '分1', '60', '1');
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('4', '4', '4', 40, '分1', '50', '1');
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('5', '5', '5', 50, '分2', '40', '1');
insert into te (ID, T_CODE, T_NAME, T_AMOUNT, T_DEPT, T_PROJECT, T_TYPE)
values ('6', '6', '6', 60, '分2', '30', '1');
select grouping(t.t_dept),t.t_dept, t.t_project, t.t_type, sum(t.t_amount) from te t
group by grouping sets ((t.t_dept, t.t_project,t.t_type), (t.t_dept,t.t_project),t.t_dept, null);
[img]http://dl.iteye.com/upload/attachment/234078/c13667ff-9b97-3d85-b1db-a8b53b945014.jpg[/img]
注意: 在存储过程中null不会正确执行 需要改成()
如
select grouping(t.t_dept),t.t_dept, t.t_project, t.t_type, sum(t.t_amount) from te t
group by grouping sets ((t.t_dept, t.t_project,t.t_type), (t.t_dept,t.t_project),t.t_dept, ());
完!