MySQL nchikota otu site na ojiji? Nkọwa zuru ezu nke otu site na ọnụ na nchekwa data MySQL

MySQL Nchikota otu site na ojiji?MySQL nchekwa dataNkọwa zuru ezu nke otu site na ọnụ ọgụgụ

MySQL GROUP BY nkwupụta

GROUP BY nkwupụta na-achịkọta nsonaazụ atọrọ dabere na otu kọlụm ma ọ bụ karịa.

Na kọlụm agbakọtara anyị nwere ike iji ọrụ COUNT, SUM, AVG, wdg.

GROUP BY syntax

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

Ngosipụta ihe atụ

Ihe atụ dị n'isiakwụkwọ a na-eji nhazi tebụl na data ndị a. Tupu iji ya, anyị nwere ike ibubata data ndị a na nchekwa 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;

Mgbe mbubata ahụ mechara nke ọma, mebie nkwupụta SQL ndị a:

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)

Anyị na-eji nkwupụta GROUP BY chịkọta tebụl data n'aha wee gụọ ndekọ ole onye ọ bụla nwere:

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

Jiri ya na mpịakọta

NA ROLLUP nwere ike mejuputa otu ọnụ ọgụgụ ahụ (SUM, AVG, COUNT...) dabere na ọnụ ọgụgụ agbakọtara.

Dịka ọmụmaatụ, anyị na-ejikọta tebụl data dị n'elu n'aha, wee gụọ ugboro ole onye ọ bụla banyere:

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)

Ndekọ NULL na-anọchi anya ọnụọgụ nbanye maka onye ọ bụla.

Anyị nwere ike iji coalesce ịtọ aha nwere ike dochie NUll, syntax coalesce:

select coalesce(a,b,c);

Nkọwa paramita: ọ bụrụ == efu, họrọ b; ọ bụrụ b== efu, họrọ c; ọ bụrụ a! = efu, họrọ a; ọ bụrụ na abc efu, laghachi efu (enweghị isi).

N'ihe atụ na-esonụ ọ bụrụ na aha efu, anyị na-eji mkpokọta kama:

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)