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

时间：2024-09-07 08:37:08

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

相关文章