Tusitusiga Tusitusiga
MySQL Fa'aopoopo vaega ile fa'aoga?MySQL databaseFa'amatalaga auiliili ole vaega ile faitau
MySQL GROUP BY faʻamatalaga
O le fa'amatalaga GROUP BY fa'aputu fa'aputuga fa'atatau ile tasi pe sili atu koluma.
I luga o le koluma tu'ufa'atasi e mafai ona tatou fa'aogaina galuega COUNT, SUM, AVG, ma isi.
VAEGA I le syntax
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
Fa'ata'ita'iga fa'ata'ita'iga
O le faʻataʻitaʻiga i lenei mataupu e faʻaogaina ai le faʻatulagaina o laulau ma faʻamaumauga o loʻo i lalo. Aʻo leʻi faʻaaogaina, e mafai ona matou faʻaulufaleina mai faʻamatalaga nei i totonu o le database.
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;
A maeʻa le faʻaulufale mai, faʻatino le faʻamatalaga SQL lea:
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)
Ona sosoo ai lea ma le fa'aogaina o le fa'amatalaga GROUP BY e fa'avasega ai le laulau fa'amaumauga i le igoa ma faitau pe fia fa'amaumauga a tagata ta'ito'atasi:
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; +--------+----------+ | name | COUNT(*) | +--------+----------+ | 小丽 | 1 | | 小明 | 3 | | 小王 | 2 | +--------+----------+ 3 rows in set (0.01 sec)
Fa'aaoga MA ROLLUP
WITH ROLLUP e mafai ona fa'atinoina fa'amaumauga tutusa (SUM, AVG, COUNT...) i luga o fa'avae o fa'amaumauga tu'ufa'atasi.
Mo se faʻataʻitaʻiga, matou te faʻavasegaina le laulau faʻamaumauga o loʻo i luga i le igoa, ona faitau lea o le aofaʻi o taimi e ulufale ai tagata taʻitasi:
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)
O le fa'amaumauga NULL o lo'o fa'atusalia le numera o saini mo tagata uma.
E mafai ona tatou faʻaoga faʻatasi e seti ai se igoa e mafai ona suitulaga i le NUll, coalesce syntax:
select coalesce(a,b,c);
Fa'amatalaga Parameter: afai a==null, filifili le b;afai b==null, filifili c;afai a!=null, filifili a;afai ole abc e null, toe fa'afo'i le null (leai se uiga).
I le faʻataʻitaʻiga o loʻo i lalo pe a leai le igoa matou te faʻaogaina le aofaʻi nai lo:
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/ ) fa'asoa "MySQL sum group e ala ile fa'aoga? Fa'amatalaga Auiliili o le Vaega e ala ile Count ile MySQL Database" e fesoasoani ia te oe.
Fa'afeiloa'i e fa'asoa le so'otaga o lenei tusitusiga:https://www.chenweiliang.com/cwl-477.html
Faʻafeiloaʻi i le Telegram channel o le blog a Chen Weiliang e maua ai faʻamatalaga lata mai!
📚 O lenei taʻiala o loʻo i ai le taua tele, 🌟O se avanoa e seasea maua, aua le misia! ⏰⌛💨
Faasoa ma fiafia pe a e fiafia i ai!
O lau fefa'asoaa'i ma le fiafia o la matou fa'aosofiaga faifaipea!