MySQL jumlah klompok miturut panggunaan? Panjelasan rinci babagan klompok miturut count ing database MySQL

MySQL Jumlah klompok miturut panggunaan?database MySQLPanjelasan rinci babagan klompok miturut count

MySQL GROUP BY statement

Pranyatan GROUP BY nglumpukake set asil adhedhasar siji utawa luwih kolom.

Ing kolom diklompokaké kita bisa nggunakake fungsi COUNT, SUM, AVG, etc.

GROUP BY sintaks

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

Tuladha demonstrasi

Tuladha ing bab iki nggunakake struktur tabel lan data ing ngisor iki.Sadurunge nggunakake, kita bisa ngimpor data ing ngisor iki menyang 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;

Sawise impor sukses, jalanake statement SQL ing ngisor iki:

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)

Sabanjure, kita nggunakake statement GROUP BY kanggo ngelompokake tabel data kanthi jeneng lan ngitung jumlah rekaman saben wong:

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

Gunakake WITH ROLLUP

WITH ROLLUP bisa ngleksanakake statistik padha (SUM, AVG, COUNT ...) ing basis saka statistik diklompokaké.

Contone, kita nglumpukake tabel data ing ndhuwur miturut jeneng, banjur ngetung kaping pirang-pirang saben wong wis mlebu:

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)

Cathetan NULL nuduhake jumlah login kanggo kabeh wong.

Kita bisa nggunakake coalesce kanggo nyetel jeneng sing bisa ngganti NUll, coalesce syntax:

select coalesce(a,b,c);

Deskripsi parameter: yen a==null, pilih b; yen b==null, pilih c; yen a!=null, pilih a; yen abc null, bali null (ora ana artine).

Ing conto ing ngisor iki yen jeneng kosong kita nggunakake total tinimbang:

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)