Ẹgbẹ apao MySQL nipasẹ lilo? Alaye alaye ti ẹgbẹ nipasẹ kika ni aaye data MySQL

MySQL Apapọ ẹgbẹ nipa lilo?MySQL databaseAlaye alaye ti ẹgbẹ nipasẹ kika

MySQL GROUP BY gbólóhùn

GROUP BY gbólóhùn ṣe akojọpọ abajade ti a ṣeto ti o da lori ọkan tabi diẹ ẹ sii awọn ọwọn.

Lori iwe akojọpọ a le lo awọn iṣẹ COUNT, SUM, AVG, ati bẹbẹ lọ.

GROUP BY sintasi

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

Apeere ifihan

Apeere ti o wa ninu ori yii nlo ilana tabili atẹle ati data. Ṣaaju lilo rẹ, a le gbe data wọnyi wọle sinu ibi ipamọ data.

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;

Lẹhin agbewọle ti ṣaṣeyọri, ṣiṣẹ alaye SQL atẹle yii:

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)

Nigbamii a lo GROUP BY alaye lati ṣe akojọpọ tabili data pẹlu orukọ ati ka iye awọn igbasilẹ ti eniyan kọọkan ni:

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

Lo PẸLU yipo

PẸLU ROLLUP le ṣe awọn iṣiro kanna (SUM, AVG, COUNT...) lori ipilẹ awọn iṣiro akojọpọ.

Fun apẹẹrẹ, a ṣe akojọpọ tabili data loke nipasẹ orukọ, lẹhinna ka iye awọn akoko ti eniyan kọọkan ti wọle:

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)

Igbasilẹ NULL duro fun nọmba awọn wiwọle fun gbogbo eniyan.

A le lo coalesce lati ṣeto orukọ kan ti o le rọpo NUll, syntax coalesce:

select coalesce(a,b,c);

Apejuwe paramita: ti o ba jẹ = asan, yan b; ti b== asan, yan c; ti o ba jẹ a!= asan, yan a; ti abc ba jẹ asan, pada asan (laisi itumọ).

Ninu apẹẹrẹ atẹle ti orukọ ba ṣofo a lo lapapọ dipo:

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)