MySQL jumlah grup ku pamakéan? Penjelasan rinci ngeunaan grup ku cacah dina database MySQL

MySQL Jumlah grup ku pamakéan?database MySQLKaterangan lengkep ngeunaan grup ku cacah

MySQL GROUP BY pernyataan

Pernyataan GROUP BY ngagolongkeun hasil set dumasar kana hiji atawa leuwih kolom.

Dina kolom dikelompokeun urang tiasa nganggo fungsi COUNT, SUM, AVG, jsb.

GROUP ku sintaksis

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

Conto démo

Conto dina ieu bab ngagunakeun struktur jeung data tabél ieu di handap.Saméméh ngagunakeun éta, urang bisa ngimpor data ieu di handap kana 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;

Saatos impor suksés, laksanakeun pernyataan SQL ieu:

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)

Teras kami nganggo pernyataan GROUP BY pikeun ngagolongkeun tabel data dumasar nami sareng ngitung sabaraha rékaman unggal jalma:

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

Paké WITH ROLLUP

WITH ROLLUP tiasa nerapkeun statistik anu sami (SUM, AVG, COUNT...) dumasar kana statistik dikelompokeun.

Contona, urang ngagolongkeun tabel data di luhur dumasar ngaran, lajeng ngitung sabaraha kali unggal jalma geus asup:

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)

Catetan NULL ngagambarkeun jumlah login pikeun sadayana.

Urang tiasa nganggo coalesce pikeun nyetél nami anu tiasa ngagentos NUll, coalesce syntax:

select coalesce(a,b,c);

Katerangan parameter: lamun a==null, pilih b; lamun b==null, pilih c; lamun a!=null, pilih a; lamun abc null, balikkeun null (teu aya hartina).

Dina conto di handap ieu lamun nami kosong kami nganggo total gantina:

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)