MySQL sum coetus ex usu? Detailed explicatio coetus per comitem in MySQL database

Quomodo MySQL SUM GROUP BY utendum est? Explicatio accurata de GROUP BY COUNT in basi datorum MySQL.

MySQL Group dicitur

Circulus per constitutiones circulos effectus pone sub una vel pluribus columnis fundatur.

In columna collata uti possumus functiones NUMERUS, SUM, AVG, etc.

GROUP BY syntax

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Exemplum demonstrationis

Exemplum in hoc capite sequenti tabula structura et notitia utitur, antequam ea utatur, sequentes notitias in datorum importare possumus.

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
--  Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
--  Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Postquam res prospere gesta est, sequentia SQL verba facere:

mysql> set names utf8;
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

Deinde utimur CORONA DICO nominando ac computando tabulam datam coetui numero quot quisque homo habet:

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

Utere cum ROLLUP

CUM ROLLUP eadem statistica (SUM, AVG, COMITI...) efficere potest ratione statisticarum aggregatarum.

Exempli gratia, suprascriptae notae tabulam nominatim glomeramus, et tunc numerum cuiusque personae initium sumemus:

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

Commentarius NULL numerus logins pro omnibus repraesentat.

Coalescere possumus ad nomen apponendum quod NUll potest reponere, syntaxin coalescere;

select coalesce(a,b,c);

Parametri descriptio: si nullus, elige b, si b== nullus, elige c, si a!= nullus, elige a, si abc nullus, nullum redde.

In hoc exemplo, si nomen vacuum est, loco totalis utimur:

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)