Ƙungiyar jimlar MySQL ta amfani? Cikakken bayani na rukuni ta ƙidaya a cikin bayanan MySQL

MySQL Ƙungiya ta hanyar amfani?MySQL databaseCikakken bayani na rukuni ta kirga

MySQL GROUP BY sanarwa

Bayanin GROUP BY yana tattara sakamakon da aka saita akan ginshiƙai ɗaya ko fiye.

A kan rukunin da aka haɗa za mu iya amfani da ayyuka COUNT, SUM, AVG, da sauransu.

GROUP BY syntax

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

Misali nuni

Misali a cikin wannan babin yana amfani da tsarin tebur da bayanai masu zuwa, kafin amfani da su, zamu iya shigo da bayanan da ke zuwa cikin ma'ajin bayanai.

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;

Bayan shigowar ya yi nasara, aiwatar da bayanin SQL mai zuwa:

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)

Bayan haka muna amfani da bayanin GROUP BY don haɗa teburin bayanai da suna kuma mu ƙidaya yawan bayanan kowane mutum yana da:

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

Yi amfani da ROLUPUP

TARE DA ROLLUP na iya aiwatar da ƙididdiga iri ɗaya (SUM, AVG, COUNT...) bisa ƙididdige ƙungiyoyin.

Misali, muna hada teburin bayanan da ke sama da suna, sannan mu kirga adadin lokutan da kowane mutum ya shiga:

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)

Rikodin NULL yana wakiltar adadin shiga ga kowa da kowa.

Za mu iya amfani da coalesce don saita sunan da zai iya maye gurbin NUll, syntax coalesce:

select coalesce(a,b,c);

Siffar siga: idan a = babu, zaɓi b; idan b== null, zaɓi c; idan a! = null, zaɓi a; idan abc ba shi da amfani, koma null (marasa ma'ana).

A cikin misali mai zuwa idan sunan babu komai muna amfani da jimlar maimakon:

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)