Foydalanish bo'yicha MySQL sum guruhi? MySQL ma'lumotlar bazasida sonlar bo'yicha guruhning batafsil tushuntirishlari

MySQL Foydalanish bo'yicha guruh yig'iladimi?MySQL ma'lumotlar bazasiGuruh bo'yicha batafsil tushuntirish

MySQL GROUP BY bayonoti

GROUP BY bayonoti natijalar to'plamini bir yoki bir nechta ustunlar asosida guruhlaydi.

Guruhlangan ustunda biz COUNT, SUM, AVG va hokazo funktsiyalardan foydalanishimiz mumkin.

GROUP BY BY sintaksis

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

Namoyish namunasi

Ushbu bobdagi misolda quyidagi jadval strukturasi va ma'lumotlardan foydalaniladi.Uni ishlatishdan oldin ma'lumotlar bazasiga quyidagi ma'lumotlarni import qilishimiz mumkin.

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;

Import muvaffaqiyatli bo'lgandan so'ng, quyidagi SQL iborasini bajaring:

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)

Keyinchalik, ma'lumotlar jadvalini nomi bo'yicha guruhlash va har bir kishida qancha yozuv borligini hisoblash uchun GROUP BY iborasidan foydalanamiz:

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

ROLLUP BILAN foydalanish

WITH ROLLUP guruhlangan statistik ma'lumotlar asosida bir xil statistik ma'lumotlarni (SUM, AVG, COUNT...) amalga oshirishi mumkin.

Misol uchun, biz yuqoridagi ma'lumotlar jadvalini nom bo'yicha guruhlaymiz va keyin har bir kishi tizimga necha marta kirganligini hisoblaymiz:

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)

NULL yozuvi hamma uchun loginlar sonini ifodalaydi.

NUll o'rnini bosadigan nomni o'rnatish uchun coalesce dan foydalanishimiz mumkin, birlashma sintaksisi:

select coalesce(a,b,c);

Parametr tavsifi: agar a== null bo'lsa, b ni tanlang; agar b== null bo'lsa, c ni tanlang, agar a!= null bo'lsa, a ni tanlang; abc null bo'lsa, nullni qaytaring (ma'nosiz).

Quyidagi misolda ism bo'sh bo'lsa, uning o'rniga jamidan foydalanamiz:

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)