Grúpa suim MySQL de réir úsáide? Míniú mionsonraithe ar ghrúpa de réir comhairimh i mbunachar sonraí MySQL

Conas MySQL SUM GROUP BY a úsáid? Míniú mionsonraithe ar GROUP BY COUNT i mbunachar sonraí MySQL.

GRÚPA MySQL De réir ráitis

Grúpálann an ráiteas GHRÚPA DE RÉIR an tacar torthaí bunaithe ar cholún amháin nó níos mó.

Ar an gcolún grúpáilte is féidir linn feidhmeanna COUNT, SUM, AVG, etc. a úsáid.

GRÚPA DE RÉIR chomhréire

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

Taispeántas samplach

Úsáideann an sampla sa chaibidil seo an struchtúr tábla agus na sonraí seo a leanas: Sula n-úsáidfimid iad, is féidir linn na sonraí seo a leanas a allmhairiú isteach sa bhunachar sonraí.

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;

Tar éis don iompórtáil a bheith rathúil, déan an ráiteas SQL seo a leanas:

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)

Ansin úsáidimid an ráiteas GROUP BY chun an tábla sonraí a ghrúpáil de réir ainm agus comhaireamh cé mhéad taifead atá ag gach duine:

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

Bain úsáid as LE ROLLUP

Is féidir LE ROLLUP na staitisticí céanna a chur i bhfeidhm (SUM, AVG, COUNT...) ar bhonn staitisticí grúpáilte.

Mar shampla, déanaimid an tábla sonraí thuas a ghrúpáil de réir ainm, agus ansin comhairimid an líon uaireanta a logáil gach duine isteach:

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)

Léiríonn an taifead NULLComment líon na logáil isteach do gach duine.

Is féidir linn úsáid a bhaint as coalesce chun ainm a shocrú a fhéadfaidh ionad NUll, comhréir a chomhleá:

select coalesce(a,b,c);

Cur síos ar an bparaiméadar: má tá a==null, roghnaigh b; más b==null, roghnaigh c; má tá a!=null, roghnaigh a; más rud é go bhfuil abc ar neamhní, seol ar ais (gan bhrí).

Sa sampla seo a leanas má tá an t-ainm folamh úsáidimid an t-iomlán ina ionad:

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)