articulus Directory
MySQL Sum coetus usus?MySQL databaseExplicatio coetus per comitem
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)
Spes Chen Weiliang Blog ( https://www.chenweiliang.com/ ) participatur "MySQL summa coetus usu? Explicatio sodalitatis a comite in MySQL Database" utile est tibi.
Grata communicare nexum huius articuli:https://www.chenweiliang.com/cwl-477.html
Grata ad canalem Telegram Chen Weiliang's blog ut tardus updates!
📚 Hic dux ingens valorem continet, 🌟 Haec rara occasio est, id non fallunt! ⏰⌛💨
Share and like si libet!
Tua communicatio et similia continua sunt motiva nostra!