简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
집 > 데이터 베이스 > MySQL 튜토리얼 > 본문

Mysql,Oracle使用rollup函数完成队列统计

WBOY
풀어 주다： 2016-06-07 16:24:20
원래의
1047명이 탐색했습니다.

Mysql,Oracle使用rollup函数完成行列统计 ??? 昨天突然在 一篇博客中看到了Mysql也有rollup函数，原博文使用了rollup进行行列统计，原博文链接如下： ??? http://www.cnblogs.com/lhj588/archive/2012/06/15/2550392.html ??? 本博文主要是记录下mysql和oracl

Mysql,Oracle使用rollup函数完成行列统计

??? 昨天突然在一篇博客中看到了Mysql也有rollup函数，原博文使用了rollup进行行列统计，原博文链接如下：

??? http://www.cnblogs.com/lhj588/archive/2012/06/15/2550392.html

??? 本博文主要是记录下mysql和oracle使用rollup函数进行行列统计，内容比较简单。

??? 首先是mysql，建表测试：

??? 

CREATE TABLE `tmysql_test_hanglietongji` (
  `id` int(11) NOT NULL,
  `c1` char(2) COLLATE utf8_bin DEFAULT NULL,
  `c2` char(2) COLLATE utf8_bin DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
로그인 후 복사

???

INSERT INTO `tmysql_test_hanglietongji` VALUES (1, 'A1', 'B1', 9);
INSERT INTO `tmysql_test_hanglietongji` VALUES (2, 'A2', 'B1', 7);
INSERT INTO `tmysql_test_hanglietongji` VALUES (3, 'A3', 'B1', 4);
INSERT INTO `tmysql_test_hanglietongji` VALUES (4, 'A4', 'B1', 2);
INSERT INTO `tmysql_test_hanglietongji` VALUES (5, 'A1', 'B2', 2);
INSERT INTO `tmysql_test_hanglietongji` VALUES (6, 'A2', 'B2', 9);
INSERT INTO `tmysql_test_hanglietongji` VALUES (7, 'A3', 'B2', 8);
INSERT INTO `tmysql_test_hanglietongji` VALUES (8, 'A4', 'B2', 5);
INSERT INTO `tmysql_test_hanglietongji` VALUES (9, 'A1', 'B3', 1);
INSERT INTO `tmysql_test_hanglietongji` VALUES (10, 'A2', 'B3', 8);
INSERT INTO `tmysql_test_hanglietongji` VALUES (11, 'A3', 'B3', 8);
INSERT INTO `tmysql_test_hanglietongji` VALUES (12, 'A4', 'B3', 6);
INSERT INTO `tmysql_test_hanglietongji` VALUES (13, 'A1', 'B4', 8);
INSERT INTO `tmysql_test_hanglietongji` VALUES (14, 'A2', 'B4', 2);
INSERT INTO `tmysql_test_hanglietongji` VALUES (15, 'A3', 'B4', 6);
INSERT INTO `tmysql_test_hanglietongji` VALUES (16, 'A4', 'B4', 9);
INSERT INTO `tmysql_test_hanglietongji` VALUES (17, 'A1', 'B4', 3);
INSERT INTO `tmysql_test_hanglietongji` VALUES (18, 'A2', 'B4', 5);
INSERT INTO `tmysql_test_hanglietongji` VALUES (19, 'A3', 'B4', 2);
INSERT INTO `tmysql_test_hanglietongji` VALUES (20, 'A4', 'B4', 5);
로그인 후 복사

?? 要完成的效果如下：

???

????? 最简单的是使用union,如下：

????? 

select ifnull(c1, 'total') as 'total',
       sum(if(c2 = 'B1', C3, 0)) AS B1,
       sum(if(c2 = 'B2', C3, 0)) AS B2,
       sum(if(c2 = 'B3', C3, 0)) AS B3,
       sum(if(c2 = 'B4', C3, 0)) AS B4,
       SUM(C3) AS TOTAL
  from tmysql_test_hanglietongji
 group by C1 
union 
select 'total' as 'total',
       sum(if(c2 = 'B1', C3, 0)) AS B1,
       sum(if(c2 = 'B2', C3, 0)) AS B2,
       sum(if(c2 = 'B3', C3, 0)) AS B3,
       sum(if(c2 = 'B4', C3, 0)) AS B4,
       SUM(C3) AS TOTAL
  from tmysql_test_hanglietongji
 order by 1
로그인 후 복사

??? 也可以使用with rollup函数。注意当使用 rollup时, 你不能同时使用 order by子句进行结果排序

??? 

select ifnull(c1, 'total') 'total',
       sum(if(c2 = 'B1', C3, 0)) AS B1,
       sum(if(c2 = 'B2', C3, 0)) AS B2,
       sum(if(c2 = 'B3', C3, 0)) AS B3,
       sum(if(c2 = 'B4', C3, 0)) AS B4,
       SUM(C3) AS TOTAL
  from tmysql_test_hanglietongji
 group by C1 with rollup;
로그인 후 복사

?? with rollup其实是第一个的简化。

?? 也可以这样写：

???

SELECT IFNULL(c1, 'total') AS total,
       SUM(IF(c2 = 'B1', c3, 0)) AS B1,
       SUM(IF(c2 = 'B2', c3, 0)) AS B2,
       SUM(IF(c2 = 'B3', c3, 0)) AS B3,
       SUM(IF(c2 = 'B4', c3, 0)) AS B4,
       SUM(IF(c2 = 'total', c3, 0)) AS total
  FROM (SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3
          FROM tmysql_test_hanglietongji 
         GROUP BY c1, c2 WITH ROLLUP
        HAVING c1 IS NOT NULL) AS A
 GROUP BY c1 WITH ROLLUP;
로그인 후 복사

??? HAVING c1 IS NOT NULL条件主要是过滤掉对整个tmysql_test_hanglietongji 表求和的那一行，以上面的子查询为例：

??? 

SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3
          FROM tmysql_test_hanglietongji 
         GROUP BY c1, c2 WITH ROLLUP
로그인 후 복사

??? 结果是：

???

?? 相当于：

??

SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3
FROM tmysql_test_hanglietongji 
GROUP BY c1, c2
union ALL
SELECT c1, 'total' AS c2, SUM(c3) AS c3
FROM tmysql_test_hanglietongji 
GROUP BY c1
union ALL
SELECT NULL, 'total' AS c2, SUM(c3) AS c3
FROM tmysql_test_hanglietongji
로그인 후 복사

??? 结果是：

???

??? 可以看出group by c1,c2 with rollup相当于group by c1,c2 union group by c1(c2替换为NULL) union?(c1,c2全部替换为NULL)。

?? 这里的替换规则参考了链接

?? http://blog.itpub.net/519536/viewspace-610995

?? 原文是替换Oracle的rollup，在Mysql中也适用。

?? 使用普通sql写法是：

?? 

SELECT IFNULL(c1, 'total') AS total,
       SUM(IF(c2 = 'B1', c3, 0)) AS B1,
       SUM(IF(c2 = 'B2', c3, 0)) AS B2,
       SUM(IF(c2 = 'B3', c3, 0)) AS B3,
       SUM(IF(c2 = 'B4', c3, 0)) AS B4,
       SUM(IF(c2 = 'total', c3, 0)) AS total
  FROM (SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3
          FROM tmysql_test_hanglietongji
         GROUP BY c1, c2
        HAVING c1 IS NOT NULL
        union
        SELECT c1, 'total' as c2, SUM(c3) AS c3
          FROM tmysql_test_hanglietongji
         group by c1) A
 group by c1
UNION
SELECT 'total' as total,
       SUM(IF(c2 = 'B1', c3, 0)) AS B1,
       SUM(IF(c2 = 'B2', c3, 0)) AS B2,
       SUM(IF(c2 = 'B3', c3, 0)) AS B3,
       SUM(IF(c2 = 'B4', c3, 0)) AS B4,
       SUM(IF(c2 = 'total', c3, 0)) AS total
  FROM (SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3
          FROM tmysql_test_hanglietongji
         GROUP BY c1, c2
        HAVING c1 IS NOT NULL
        union
        SELECT c1, 'total' as c2, SUM(c3) AS c3
          FROM tmysql_test_hanglietongji
         group by c1) A
로그인 후 복사

?? 少了一个是因为上面的having要求c1 is not null,所以替换c1为NULL就没有了。

?

?? 下面看下oracle中怎么写，想要的效果如图：

???

?? 首先建表。

??

create table TSQL_TEST_HANGLIETONGJI
(
  ID NUMBER(4) not null,
  C1 VARCHAR2(2),
  C2 VARCHAR2(2),
  C3 NUMBER(4)
)
;
alter table TSQL_TEST_HANGLIETONGJI
  add primary key (ID);

insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (1, 'A1', 'B1', 9);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (2, 'A2', 'B1', 7);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (3, 'A3', 'B1', 4);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (4, 'A4', 'B1', 2);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (5, 'A1', 'B2', 2);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (6, 'A2', 'B2', 9);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (7, 'A3', 'B2', 8);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (8, 'A4', 'B2', 5);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (9, 'A1', 'B3', 1);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (10, 'A2', 'B3', 8);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (11, 'A3', 'B3', 8);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (12, 'A4', 'B3', 6);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (13, 'A1', 'B4', 8);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (14, 'A2', 'B4', 2);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (15, 'A3', 'B4', 6);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (16, 'A4', 'B4', 9);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (17, 'A1', 'B4', 3);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (18, 'A2', 'B4', 5);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (19, 'A3', 'B4', 2);
insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)
values (20, 'A4', 'B4', 5);
로그인 후 복사

?? 最简单的写法是：

???

select c1,
       sum(decode(c2,'B1', C3, 0)) AS B1,
       sum(decode(c2 ,'B2', C3, 0)) AS B2,
       sum(decode(c2 ,'B3', C3, 0)) AS B3,
       sum(decode(c2 ,'B4', C3, 0)) AS B4,
       SUM(C3) AS TOTAL
  from tsql_test_hanglietongji
 group by C1
UNION
SELECT 'TOTAL',
       sum(decode(c2 ,'B1', C3, 0)) AS B1,
       sum(decode(c2 ,'B2', C3, 0)) AS B2,
       sum(decode(c2 ,'B3', C3, 0)) AS B3,
       sum(decode(c2 ,'B4', C3, 0)) AS B4,
       SUM(C3)
  FROM tsql_test_hanglietongji
로그인 후 복사

?? 然后使用rollup函数简化。

???

SELECT nvl(c1, 'total') AS total,
       SUM(decode(c2, 'B1', c3, 0)) AS B1,
       SUM(decode(c2, 'B2', c3, 0)) AS B2,
       SUM(decode(c2, 'B3', c3, 0)) AS B3,
       SUM(decode(c2, 'B4', c3, 0)) AS B4,
       sum(c3) AS total
  FROM tsql_test_hanglietongji
 GROUP BY ROLLUP(c1)
로그인 후 복사

???也可以这么写：

??

SELECT nvl(c1, 'total') AS total_c,
       SUM(decode(c2, 'B1', c3, 0)) AS B1,
       SUM(decode(c2, 'B2', c3, 0)) AS B2,
       SUM(decode(c2, 'B3', c3, 0)) AS B3,
       SUM(decode(c2, 'B4', c3, 0)) AS B4,
       SUM(decode(c2, 'total', c3, 0)) AS total_r
  FROM (SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3
          FROM tsql_test_hanglietongji
         GROUP BY ROLLUP(c1, c2)
        HAVING c1 IS NOT NULL) A
 GROUP BY ROLLUP(c1);
로그인 후 복사

? rollup和普通sql替换上面也说了，举个例子：

?? 

SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3
          FROM tsql_test_hanglietongji
         GROUP BY ROLLUP(c1, c2)
로그인 후 복사

? 效果是：

??

?? 普通sql写法是：

??

SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3
  FROM tsql_test_hanglietongji
 GROUP BY c1, c2
union all
SELECT c1, nvl(null, 'total') AS c2, SUM(c3) AS c3
  FROM tsql_test_hanglietongji
 GROUP BY c1
union all
SELECT NULL, 'total' AS c2, SUM(c3) AS c3
  FROM tsql_test_hanglietongji
 order by 1, 2
로그인 후 복사

??? 细心的朋友也许注意到了，第二个union all带了order by 1,2而上面的mysql没有带order by,这和mysql和oracle对NULL的默认排序规则有关。

??? 使用普通sql重写rollup为：

???

SELECT nvl(c1, 'total') AS total_c,
       SUM(decode(c2, 'B1', c3, 0)) AS B1,
       SUM(decode(c2, 'B2', c3, 0)) AS B2,
       SUM(decode(c2, 'B3', c3, 0)) AS B3,
       SUM(decode(c2, 'B4', c3, 0)) AS B4,
       SUM(decode(c2, 'total', c3, 0)) AS total_r
  FROM (SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3
          FROM tsql_test_hanglietongji
         GROUP BY c1, c2
        HAVING c1 IS NOT NULL
        union all
        SELECT c1, nvl(null, 'total') AS c2, SUM(c3) AS c3
          FROM tsql_test_hanglietongji
         GROUP BY c1
        HAVING c1 IS NOT NULL) A
 GROUP BY c1
union all
SELECT nvl(null, 'total') AS total_c,
       SUM(decode(c2, 'B1', c3, 0)) AS B1,
       SUM(decode(c2, 'B2', c3, 0)) AS B2,
       SUM(decode(c2, 'B3', c3, 0)) AS B3,
       SUM(decode(c2, 'B4', c3, 0)) AS B4,
       SUM(decode(c2, 'total', c3, 0)) AS total_r
  FROM (SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3
          FROM tsql_test_hanglietongji
         GROUP BY c1, c2
        HAVING c1 IS NOT NULL
        union all
        SELECT c1, nvl(null, 'total') AS c2, SUM(c3) AS c3
          FROM tsql_test_hanglietongji
         GROUP BY c1
        HAVING c1 IS NOT NULL) A
 order by 1
로그인 후 복사

?? 这里也排除了c1 is null的情况。

??? 通过上面的对比，发现oracle和mysql的rollup非常相似，对rollup函数感兴趣的朋友请仔细搜索rollup学习。

??? 到这里该结束了，有任何意见请留言，如文中sql有错误也请指出，谢谢。

??? 全文完。

?

?

??

?

?

?

?

?

???

?

관련 라벨：
mysql oracle rollup 使用 函数 마치다
원천：php.cn
이전 기사：MySQL CAST与CONVERT 函数的用法 다음 기사：MySQL获取某个时间范围内的数据
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
저자별 최신 기사
최신 이슈
결과 집합에서 가장 낮은 값만 표시하는 방법(MYSQL) 나는 다음과 같은 진술을 가지고 있습니다 : selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'frommes...
에서 2024-04-06 21:44:19
0
1
603
MySQL SQL 쿼리를 사용하여 다른 테이블의 필드 합계를 계산합니다. 다음과 같은 스키마가 있습니다. "user_id" 및 "username" 속성이 있는 사용자 테이블과 "custome...
에서 2024-04-06 19:39:29
0
1
441
MySQL: "<여기 변수 또는 하위 쿼리>로...에서 텍스트 선택" 다음 데이터가 포함된 다음 테이블이 있습니다. id 텍스트 언어 1 독일어 텍스트 독일어 2 영어 텍스트 영어 내가 원하는 것은 다음 형식의 결과를 얻는 것입니다...
에서 2024-04-06 19:25:54
0
2
529
MySQL에서 그룹화하고 계산하는 방법은 무엇입니까? 친구에게 보낸 삭제되지 않은 메시지의 총 개수를 추출하는 쿼리를 작성하려고 합니다. 다음은 테이블 구조의 스크린샷입니다. 제가 달성하려는 출력은 다음과 같습니다....
에서 2024-04-06 18:30:17
0
1
353
MySQL은 여러 테이블에서 데이터를 가져옵니다. 다음 열이 포함된 eg_design 테이블이 있습니다. 다음 열이 포함된 eg_domains 테이블과 다음 열이 포함된 eg_fonts 테이블이 있습니다. 이제 ...
에서 2024-04-06 18:42:44
0
2
479
관련 주제
더>
인기 추천
인기 튜토리얼
더>
관련 튜토리얼
인기 추천
최신 강좌
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿
회사 소개 부인 성명 Sitemap
PHP 중국어 웹사이트：공공복지 온라인 PHP 교육，PHP 학습자의 빠른 성장을 도와주세요！