[MySQL]group by 与 having 结合函数 的统计技巧

group by 与 having 允许字段使用函数，根据函数运行的结果group by分组或having设置选择条件;

同时group by 与 having 也允许使用字段别名

示例表a:

id	a	effective_date
38	1	2018-03-23 09:55:58
39	9	2018-03-22 09:56:04
66	1	2018-02-21 09:56:09
68	6	2018-01-13 09:56:17
69	1	0000-00-00 00:00:00

示例表b:

id	u_id	amount	t4_date
1	39	30	2017-07-28 15:30:00
2	66	21	2018-03-08 10:07:40
3	69	13	2018-03-01 10:09:00

 

应用示例：

1.单表格式化分组

SELECT sum(a) FROM test_1 GROUP BY DATE_FORMAT(effective_date,"%Y-%m");

sum(a)

1

6

1

10

2.单表格式化分组（使用别名）

SELECT DATE_FORMAT(effective_date,"%Y-%m") AS date, sum(a) FROM test_1 GROUP BY date;

date	sum(a)
0000-00	1
2018-01	6
2018-02	1
2018-03	10

3.单表格式化分组，having选择输出的行（使用别名）

SELECT DATE_FORMAT(effective_date,"%Y-%m") AS date, sum(a) FROM test_1 GROUP BY date HAVING date = "2018-02";

date	sum(a)
2018-02	1

4.连表应用

SELECT DATE_FORMAT(b.t4_date,"%Y-%m") AS date, sum(a.a) FROM test_1 AS a LEFT JOIN test_4 AS b ON a.id = b.u_id GROUP BY date HAVING date = "2018-03";

date	sum(a.a)
2018-03	2