SQL 語法統計年,月,季

引用：[SQL]如何下SQL語法統計年,月,季的資料
資料表如下：

account	loginTime
five	2008-06-05 10:24:12
...	...

比較笨的方法是一行一行讀出後再用程式做整理
比較聰明的方法是用SQL語法直接分年,月,季取出資料來
假設我們要查詢的時間範圍是2008-01-01~2008-12-31
用MySQL環境之下所下的指令為
年:
select year(logintime) as year,count(distinct(account)) as total
from logintime
WHERE logintime between '2008-01-01' and '2008-12-31'
group by year(logintime)

year	total
2008	1

季:
select year(logintime) as year,CEILING(month(logintime)/3) as season, count(distinct(account)) as total
from logintime
WHERE logintime between '2008-1-01' and '2008-12-31'
group by year(logintime),CEILING(month(logintime)/3)

year	season	total
2008	2	1

月:
select year(logintime) as year, month(logintime) as month, count(distinct(account)) as total
from logintime
WHERE logintime between '2008-1-01' and '2008-12-31'
group by year(logintime),month(logintime)

year	month	total
2008	6	1

mysql function解說：
1.distinct :表示不取重複資料
2.CEILING :表示無條件進位成整數
3.year:表示取年
4.month:表示取月
5.count:表示取筆數