需求:
撈取3月份儲值總金額大於等於5000點的資料 。
兩張表,部分結構如下:
id | mid | point | pay_time |
xxxx | xxxx | xxx | xxxx |
xxx | xxxx | xxxx | xxxx |
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。
版权声明:本文为u014096278原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。