[MySQL]group by 與 having 結合函數 的統計技巧

標籤：div   group   pos   amount   格式   class   group by   font   border   

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

[MySQL]group by 與 having 結合函數 的統計技巧