Mysql中的WITH ROLLUP用法

Mysql中的WITH ROLLUP用法

1.WITH ROLLUP：在group分组字段的基础上再进行统计数据。

例子：首先在name字段上进行分组，然后在分组的基础上进行某些字段统计，表结构如下：

CREATE TABLE `test` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(25) DEFAULT NULL COMMENT '标题',
  `uid` int(11) DEFAULT NULL COMMENT 'uid',
  `money` decimal(2,0) DEFAULT '0',
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

存几条数据看看：

INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('2', '国庆节', '2', '12', '周伯通');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('3', '这次是8天假哦', '3', '33', '老顽童');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('4', '这是Uid=1的第一条数据哦', '1', '70', '欧阳锋');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('5', '灵白山少主', '4', '99', '欧阳克');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('7', '九阴真经创始人', '3', '12', '小顽童');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('8', '双手互博', '2', '56', '周伯通');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('9', '销魂掌', '2', '19', '周伯通');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('10', '蛤蟆功', '1', '57', '欧阳锋');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('11', '绝杀掌', '3', '800', '小顽童');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('12', '九阴真经', '3', '84', '老顽童');

分组统计：

SELECT name, SUM(money) as money FROM  test GROUP BY name WITH ROLLUP;

可以看到按照name分组后对money求和统计了。上面看到 null 1242, 如何搞个别名字段比如 总金额：1242呢？也可以滴，咱们继续：

coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

SELECT coalesce(name, '总金额'),name, SUM(money) as money FROM  test GROUP BY name WITH ROLLUP;

上面可以看出，在数据汇总方面。用途还是很方便滴。

 

=========================================================================================================

 

• 当用select*from 表名 group by  ‘字段名1’ 将选出来的内容将按照字段1分组，其他列不尽相同，会以最前面的内容显示：

• GROUP BY 后面可以跟多个列名，表示以多列作为分组依据,   然后再跟with  rollup，表示在进行分组统计的基础上再次进行汇总统计（在每个分组下都会有统计汇总）：

•  

1.  

2. mysql> select name,cat,fish,count(*) from r group by name,cat,fish with rollup;

3. +------+------+------+----------+

4. | name | cat | fish | count(*) |

5. +------+------+------+----------+

6. | 小红 | x | 1 | 1 |

7. | 小红 | x | 3 | 1 |

8. | 小红 | x | NULL | 2 |

9. | 小红 | y | 1 | 1 |

10. | 小红 | y | 2 | 1 |

11. | 小红 | y | NULL | 2 |

12. | 小红 | NULL | NULL | 4 |

13. | 小明 | x | 1 | 1 |

14. | 小明 | x | 2 | 1 |

15. | 小明 | x | 4 | 1 |

16. | 小明 | x | NULL | 3 |

17. | 小明 | y | 2 | 2 |

18. | 小明 | y | NULL | 2 |

19. | 小明 | z | 1 | 1 |

20. | 小明 | z | NULL | 1 |

21. | 小明 | NULL | NULL | 6 |

22. | 小青 | y | 3 | 1 |

23. | 小青 | y | NULL | 1 |

24. | 小青 | z | 1 | 1 |

25. | 小青 | z | 2 | 1 |

26. | 小青 | z | NULL | 2 |

27. | 小青 | NULL | NULL | 3 |

28. | NULL | NULL | NULL | 13 |

29. +------+------+------+----------+

30. 23 rows in set (0.07 sec)

31.  

32. mysql> select name,cat,fish,sum(fish) from r group by name with rollup;

33. +------+-----+------+-----------+

34. | name | cat | fish | sum(fish) |

35. +------+-----+------+-----------+

36. | 小红 | x | 1 | 7 |

37. | 小明 | x | 1 | 12 |

38. | 小青 | y | 3 | 6 |

39. | NULL | y | 3 | 25 |

40. +------+-----+------+-----------+

41. 4 rows in set (0.05 sec)

42.  

43. mysql> select name,cat,fish,sum(fish) from r group by name,cat with rollup;

44. +------+------+------+-----------+

45. | name | cat | fish | sum(fish) |

46. +------+------+------+-----------+

47. | 小红 | x | 1 | 4 |

48. | 小红 | y | 1 | 3 |

49. | 小红 | NULL | 1 | 7 |

50. | 小明 | x | 1 | 7 |

51. | 小明 | y | 2 | 4 |

52. | 小明 | z | 1 | 1 |

53. | 小明 | NULL | 1 | 12 |

54. | 小青 | y | 3 | 3 |

55. | 小青 | z | 2 | 3 |

56. | 小青 | NULL | 2 | 6 |

57. | NULL | NULL | 2 | 25 |

58. +------+------+------+-----------+

59. 11 rows in set (0.06 sec)

60.  

61. mysql> select name,cat,fish,sum(fish) from r group by name,cat,fish with rollup;

62. +------+------+------+-----------+

63. | name | cat | fish | sum(fish) |

64. +------+------+------+-----------+

65. | 小红 | x | 1 | 1 |

66. | 小红 | x | 3 | 3 |

67. | 小红 | x | NULL | 4 |

68. | 小红 | y | 1 | 1 |

69. | 小红 | y | 2 | 2 |

70. | 小红 | y | NULL | 3 |

71. | 小红 | NULL | NULL | 7 |

72. | 小明 | x | 1 | 1 |

73. | 小明 | x | 2 | 2 |

74. | 小明 | x | 4 | 4 |

75. | 小明 | x | NULL | 7 |

76. | 小明 | y | 2 | 4 |

77. | 小明 | y | NULL | 4 |

78. | 小明 | z | 1 | 1 |

79. | 小明 | z | NULL | 1 |

80. | 小明 | NULL | NULL | 12 |

81. | 小青 | y | 3 | 3 |

82. | 小青 | y | NULL | 3 |

83. | 小青 | z | 1 | 1 |

84. | 小青 | z | 2 | 2 |

85. | 小青 | z | NULL | 3 |

86. | 小青 | NULL | NULL | 6 |

87. | NULL | NULL | NULL | 25 |

88. +------+------+------+-----------+

89. 23 rows in set (0.07 sec)

90.