MySQL sum pab pawg los ntawm kev siv? Kev piav qhia ntxaws ntawm pab pawg los ntawm suav hauv MySQL database

Yuav siv SUM GROUP BY li cas hauv MySQL ? Kev piav qhia ntxaws txog GROUP BY COUNT hauv MySQL database.

MySQL GROUP BY nqe lus

GROUP BY nqe lus pab pawg cov txiaj ntsig tau teeb tsa raws li ib lossis ntau kab.

Nyob rau hauv pawg pawg peb tuaj yeem siv COUNT, SUM, AVG, thiab lwm yam haujlwm.

GROUP BY syntax

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

Piv txwv ua qauv qhia

Piv txwv hauv tshooj no siv cov qauv lus hauv qab no thiab cov ntaub ntawv ua ntej siv, peb tuaj yeem nqa cov ntaub ntawv hauv qab no rau hauv cov ntaub ntawv.

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;

Tom qab ntshuam ua tiav, ua raws li cov lus hauv qab no SQL:

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)

Tom ntej no peb siv GROUP BY nqe lus los pab pawg cov ntaub ntawv los ntawm lub npe thiab suav pes tsawg cov ntaub ntawv txhua tus neeg muaj:

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

Siv ROLLUP

Nrog ROLLUP tuaj yeem siv tib cov txheeb cais (SUM, AVG, COUNT ...) raws li kev txheeb cais pawg.

Piv txwv li, peb pab pawg cov ntaub ntawv saum toj no los ntawm lub npe, thiab tom qab ntawd suav cov sij hawm uas txhua tus neeg tau nkag rau hauv:

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)

Cov ntaub ntawv NULL sawv cev rau tus lej nkag rau txhua tus.

Peb tuaj yeem siv coalesce los teeb tsa lub npe uas tuaj yeem hloov NUll, coalesce syntax:

select coalesce(a,b,c);

Parameter piav qhia: yog a ==null, xaiv b; yog b==null, xaiv c; yog a!=null, xaiv a; yog abc yog null, rov null (tsis muaj qab hau).

Hauv qhov piv txwv hauv qab no yog lub npe khoob peb siv tag nrho cov hloov pauv:

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)