Artikel Directory
MySQL Som groep troch gebrûk?MySQL databankDetaillearre útlis fan groep by count
MySQL GROUP BY statement
De GROUP BY-statement groepearret de resultaatset basearre op ien of mear kolommen.
Op de groepearre kolom kinne wy COUNT, SUM, AVG, ensfh. funksjes brûke.
GROUP BY syntaksis
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
Foarbyld demonstraasje
It foarbyld yn dit haadstik brûkt de folgjende tabelstruktuer en gegevens: Foardat wy it brûke, kinne wy de folgjende gegevens ymportearje yn de databank.
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;
Nei it ymportearjen is suksesfol, útfiere de folgjende SQL-statement:
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)
Dêrnei brûke wy de GROUP BY-statement om de gegevenstabel op namme te groepearjen en te tellen hoefolle records elke persoan hat:
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; +--------+----------+ | name | COUNT(*) | +--------+----------+ | 小丽 | 1 | | 小明 | 3 | | 小王 | 2 | +--------+----------+ 3 rows in set (0.01 sec)
Brûk MEI ROLLUP
WITH ROLLUP kin útfiere deselde statistiken (SUM, AVG, COUNT ...) op basis fan groepearre statistyk.
Wy groepearje bygelyks de boppesteande gegevenstabel op namme, en telle dan it oantal kearen dat elke persoan hat oanmeld:
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)
It rekord NULL stiet foar it oantal oanmeldingen foar elkenien.
Wy kinne koalesearje brûke om in namme yn te stellen dy't NUll kin ferfange, syntaksis koalesearje:
select coalesce(a,b,c);
Parameterbeskriuwing: as a==nul, kies b; as b==null, kies c; as a!=nul, kies a; as abc null is, werom nul (betsjuttend).
Yn it folgjende foarbyld as de namme leech is, brûke wy it totaal ynstee:
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)
Hope Chen Weiliang Blog ( https://www.chenweiliang.com/ ) dield "MySQL-somgroep troch gebrûk? Detaillearre taljochting fan groep op tel yn MySQL-database" is nuttich foar jo.
Wolkom om de keppeling fan dit artikel te dielen:https://www.chenweiliang.com/cwl-477.html
Wolkom by it Telegram-kanaal fan Chen Weiliang's blog om de lêste updates te krijen!
📚 Dizze hantlieding befettet enoarme wearde, 🌟Dit is in seldsume kâns, mis it net! ⏰⌛💨
Diel en like as jo wolle!
Jo dielen en likes binne ús trochgeande motivaasje!