oracle分组求小计、合计

1、oracle关于分组小计再合计

CREATE TABLE "NEWCA"."test"
(
"ID" BIGINT IDENTITY(1, 1) NOT NULL,
"SORT_CODE" VARCHAR(50),
"PRICE" VARCHAR(50),
"NUM" INTEGER,
CLUSTER PRIMARY KEY("ID"),
UNIQUE("ID")) STORAGE(ON "CA_DATA", CLUSTERBTR) ;COMMENT ON TABLE "NEWCA"."TEST" IS '测试一下';
COMMENT ON COLUMN "NEWCA"."TEST"."SORT_CODE" IS '分类编码';
COMMENT ON COLUMN "NEWCA"."TEST"."PRICE" IS '单价';
COMMENT ON COLUMN "NEWCA"."TEST"."NUM" IS '数量';insert into "NEWCA"."test" ("ID","SORT_CODE","PRICE","NUM") values (6, '115436', '12', 2);
insert into "NEWCA"."test" ("ID","SORT_CODE","PRICE","NUM") values (7, '1123', '10', 1);
insert into "NEWCA"."test" ("ID","SORT_CODE","PRICE","NUM") values (8, '115342', '8', 3);
insert into "NEWCA"."test" ("ID","SORT_CODE","PRICE","NUM") values (9, '222', '8', 2);
insert into "NEWCA"."test" ("ID","SORT_CODE","PRICE","NUM") values (10, '33789', '12', 2);
insert into "NEWCA"."test" ("ID","SORT_CODE","PRICE","NUM") values (11, '33443', '2', 3);
insert into "NEWCA"."test" ("ID","SORT_CODE","PRICE","NUM") values (12, '33876', '6', 4);
insert into "NEWCA"."test" ("ID","SORT_CODE","PRICE","NUM") values (13, '33965', '11', 5);

2、使用rollup查询sql：

select decode(grouping(substr(sort_code,0,2))+grouping(sort_code),0,sort_code,1,'小计','合计'),sum(price),sum(num)
from test
group by rollup(substr(sort_code,0,2),sort_code)
order by substr(sort_code,0,2),grouping(sort_code)

3、结果：

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！