group by,having,sum的使用例子

需求：

    撈取3月份儲值總金額大於等於5000點的資料 。

 

兩張表，部分結構如下：

儲值表pays

id	mid	point	pay_time
xxxx	xxxx	xxx	xxxx
xxx	xxxx	xxxx	xxxx

會員表members

id	user_name
xxxx	xxxx
xxxx	xxx

1.使用group by 先分組查看3月份每個會員的儲值記錄。

select m.user_name as 帳號,p.point as 點數 from pays as p left join members as m on m.id = p.mid where pay_time between '2014-03-01 00:00:00' and '2014-03-31 23:59:59' group by p.mid

group by 是針對某個列欄位進行分組，格式如下：

SELECT "栏位1", SUM("栏位2")
FROM "表格名"
GROUP BY "栏位1"

2.使用sum統計每個會員儲值的總數

select m.user_name as 帳號,sum(p.point) as 總金額 from pays as p left join members as m on m.id = p.mid where pay_time between '2014-03-01 00:00:00' and '2014-03-31 23:59:59' group by p.mid

sum是針對上面分組的每個相同會員的point進行相加。

3.使用having統計儲值金額大於5000點記錄,並針對總金額進行降序排序

select m.user_name as 帳號,sum(p.point) as 總金額 from pays as p left join members as m on m.id = p.mid where pay_time between '2014-03-01 00:00:00' and '2014-03-31 23:59:59' group by p.mid having sum(p.point) >= 5000 order by sum(p.point) desc

where與 having都是指定對某些條件的篩選，having是對分組後的結果進行篩選，數據庫是先處理完where的條件，再處理having。