MySQL sum group nekushandisa? Tsananguro yakadzama yeboka nekuverenga muMySQL database

MySQL Kudii boka nekushandiswa?MySQL databaseTsananguro yakadzama yeboka nekuverenga

MySQL GROUP BY chirevo

The GROUP BY statement inobatanidza mibairo yakatarwa zvichienderana nechikamu chimwe chete kana kupfuura.

Pachikamu chakabatanidzwa tinogona kushandisa COUNT, SUM, AVG, nezvimwe.

GROUP BY syntax

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

Muenzaniso kuratidzwa

Muenzaniso uri muchitsauko chino unoshandisa chimiro chetafura inotevera uye data.Tisati taishandisa, tinogona kupinza data rinotevera mudura.

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;

Mushure mekunge kupinza kwabudirira, ita zvinotevera SQL chirevo:

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)

Tevere tinoshandisa iyo GROUP BY chirevo kuunganidza tafura yedata nemazita uye kuverenga kuti marekodhi mangani munhu wese ane:

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

Shandisa NE ROLLUP

NEROLLUP inogona kuita zviverengero zvakafanana (SUM, AVG, COUNT...) pahwaro hwenhamba dzakaiswa mumapoka.

Semuenzaniso, isu tinounganidza tafura yedata iri pamusoro nemazita, tobva taverenga nhamba yenguva dzakapinda munhu wega wega:

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)

Iyo rekodhi NULL inomiririra huwandu hwema logins kune wese munhu.

Isu tinogona kushandisa coalesce kuseta zita rinogona kutsiva NUll, coalesce syntax:

select coalesce(a,b,c);

Tsanangudzo yeparameter: kana a==isina, sarudza b; kana b==isina, sarudza c; kana a!=isina, sarudza a; kana abc isiri, dzoka null (zvisina zvazvinoreva).

Mumuenzaniso unotevera kana zita risina chinhu isu tinoshandisa iyo yakazara pachinzvimbo:

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)