Kikundi cha jumla cha MySQL kwa matumizi? Maelezo ya kina ya kikundi kwa hesabu katika hifadhidata ya MySQL

Jinsi ya kutumia SUM GROUP BY katika MySQL ? Maelezo ya kina ya GROUP BY COUNT katika hifadhidata ya MySQL.

MySQL GROUP KWA taarifa

Taarifa ya GROUP BY huweka pamoja matokeo kulingana na safu wima moja au zaidi.

Kwenye safu iliyopangwa tunaweza kutumia vitendaji COUNT, SUM, AVG, n.k.

KUNDI KWA sintaksia

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

Maonyesho ya mfano

Mfano katika sura hii hutumia muundo wa jedwali na data ifuatayo. Kabla ya kuitumia, tunaweza kuingiza data ifuatayo kwenye hifadhidata.

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;

Baada ya uagizaji kufanikiwa, tekeleza taarifa ifuatayo ya 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)

Kisha tunatumia taarifa ya GROUP BY kupanga jedwali la data kwa jina na kuhesabu ni rekodi ngapi kila mtu anazo:

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

Tumia NA ROLLUP

NA ROLLUP inaweza kutekeleza takwimu sawa (SUM, AVG, COUNT...) kwa misingi ya takwimu zilizowekwa katika makundi.

Kwa mfano, tunapanga jedwali la data hapo juu kwa jina, na kisha kuhesabu idadi ya mara ambazo kila mtu ameingia:

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)

Rekodi NULL inawakilisha idadi ya watu walioingia kwa kila mtu.

Tunaweza kutumia coalesce kuweka jina ambalo linaweza kuchukua nafasi ya NUll, syntax ya coalesce:

select coalesce(a,b,c);

Maelezo ya kigezo: ikiwa a==null, chagua b; ikiwa b==null, chagua c; ikiwa a!=null, chagua a; ikiwa abc ni batili, rudisha null (bila maana).

Katika mfano ufuatao ikiwa jina ni tupu tunatumia jumla badala yake:

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)