MySQL sum group amin'ny fampiasana? Fanazavana amin'ny antsipiriany momba ny vondrona amin'ny isa ao amin'ny database MySQL

MySQL Amboary ny vondrona amin'ny fampiasana?MySQL databaseFanazavana amin'ny antsipiriany momba ny vondrona amin'ny isa

MySQL GROUP BY fanambarana

Ny fanambarana GROUP BY dia manambatra ny vokatra voafantina mifototra amin'ny tsanganana iray na maromaro.

Ao amin'ny tsanganana voavondrona dia afaka mampiasa fiasa COUNT, SUM, AVG, sns.

GROUP BY syntax

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

Fampisehoana ohatra

Ny ohatra ato amin'ity toko ity dia mampiasa ireto rafitra sy angon-drakitra manaraka ireto. Alohan'ny hampiasana azy dia azontsika atao ny manafatra ireto angona manaraka ireto ao anaty tahiry.

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;

Rehefa vita ny fanafarana dia tanteraho ity fanambarana SQL manaraka ity:

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)

Avy eo dia mampiasa ny fanambarana GROUP BY izahay mba hanangonana ny latabatra data amin'ny anarana ary hanisa ny isan'ny rakitra ananan'ny olona tsirairay:

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

Ampiasao miaraka amin'ny ROLLUP

WITH ROLLUP dia afaka mampihatra antontanisa mitovy (SUM, AVG, COUNT...) miorina amin'ny antontan'isa voavondrona.

Ohatra, atambatra amin'ny anarana ny latabatra angona etsy ambony, ary manisa ny isan'ny nidiran'ny olona tsirairay:

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)

Ny rakitra NULL dia maneho ny isan'ny fidirana ho an'ny rehetra.

Afaka mampiasa coalesce isika mba hametrahana anarana afaka manolo ny NUll, coalesce syntax:

select coalesce(a,b,c);

Famaritana parameter: raha a==null, mifidiana b; raha b==null, mifidiana c; raha a!=null, mifidiana a; raha abc dia tsy misy, avereno null (tsy misy dikany).

Amin'ity ohatra manaraka ity raha foana ny anarana dia mampiasa ny total isika fa tsy:

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)