MySQL sum group pogwiritsa ntchito? Kufotokozera mwatsatanetsatane gulu powerengera mu database ya MySQL

MySQL Kuwerengera gulu pogwiritsa ntchito?MySQL databaseKufotokozera mwatsatanetsatane gulu ndi kuwerenga

MySQL GROUP BY statement

Chiganizo cha GROUP BY chimagawa zotsatira kutengera ndime imodzi kapena zingapo.

Pagulu lamagulu titha kugwiritsa ntchito COUNT, SUM, AVG, ndi zina.

GROUP BY syntax

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

Chitsanzo chionetsero

Chitsanzo cha mutuwu chimagwiritsa ntchito ndondomeko ya tebulo ili ndi deta.Tisanagwiritse ntchito, tikhoza kuitanitsa deta yotsatirayi mu 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;

Kulowetsako kukachita bwino, chitani mawu awa a SQL:

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)

Kenako timagwiritsa ntchito chiganizo cha GROUP BY kuti tigawanitse tebulo la data ndi dzina ndikuwerengera kuchuluka kwa marekodi omwe aliyense ali nawo:

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

Gwiritsani ntchito NDI ROLLUP

NDI ROLLUP mutha kugwiritsa ntchito ziwerengero zomwezo (SUM, AVG, COUNT...) pamaziko a ziwerengero zamagulu.

Mwachitsanzo, timayika mndandanda wazomwe zili pamwambapa ndi dzina, kenaka timawerengera nthawi yomwe munthu aliyense walowamo:

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)

Zolemba NULL zimayimira kuchuluka kwa malowedwe a aliyense.

Titha kugwiritsa ntchito coalesce kukhazikitsa dzina lomwe lingalowe m'malo mwa NUll, coalesce syntax:

select coalesce(a,b,c);

Kufotokozera kwa parameter: ngati a== null, sankhani b; ngati b==null, sankhani c; ngati a!=null, sankhani a; ngati abc ilibe, bwererani (zopanda tanthauzo).

Muchitsanzo chotsatirachi ngati dzina lilibe kanthu timagwiritsa ntchito zonse m'malo mwake:

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)