数据查询分组、按月份，天，小时

SELECT year(payment_date),sum(CASE month(payment_date) WHEN 01 THEN amount END) '一月份销售额',sum(CASE month(payment_date) WHEN 02 THEN amount END) '二月份销售额',sum(CASE month(payment_date) WHEN 03 THEN amount END) '三月份销售额',sum(CASE month(payment_date) WHEN 04 THEN amount END) '四月份销售额',sum(CASE month(payment_date) WHEN 05 THEN amount END) '五月份销售额',sum(CASE month(payment_date) WHEN 06 THEN amount END) '六月份销售额',sum(CASE month(payment_date) WHEN 07 THEN amount END) '七月份销售额',sum(CASE month(payment_date) WHEN 08 THEN amount END) '八月份销售额',sum(CASE month(payment_date) WHEN 09 THEN amount END) '九月份销售额',sum(CASE month(payment_date) WHEN 10 THEN amount END) '十月份销售额',sum(CASE month(payment_date) WHEN 11 THEN amount END) '十一月份销售额',sum(CASE month(payment_date) WHEN 12 THEN amount END) '十二月份销售额'from payment group by year(payment_date);

知识关键词：DATE_FORMAT

select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks; 

select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days; 

select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months;

DATE_FORMAT(date,format)

根据format字符串格式化date值。下列修饰符可以被用在format字符串中：

%M 月名字(January……December)

%W 星期名字(Sunday……Saturday)

%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）

%Y 年, 数字, 4 位   www.2cto.com 

%y 年, 数字, 2 位

%a 缩写的星期名字(Sun……Sat)

%d 月份中的天数, 数字(00……31)

%e 月份中的天数, 数字(0……31)

%m 月, 数字(01……12)

%c 月, 数字(1……12)

%b 缩写的月份名字(Jan……Dec)

%j 一年中的天数(001……366)

%H 小时(00……23)

%k 小时(0……23)

%h 小时(01……12)

%I 小时(01……12)

%l 小时(1……12)

%i 分钟, 数字(00……59)

%r 时间,12 小时(hh:mm:ss [AP]M)

%T 时间,24 小时(hh:mm:ss)

%S 秒(00……59)

%s 秒(00……59)

%p AM或PM

%w 一个星期中的天数(0=Sunday ……6=Saturday ）

%U 星期(0……52), 这里星期天是星期的第一天

%u 星期(0……52), 这里星期一是星期的第一天

%% 一个文字“%”。

 

mysql

 

 

--按照月份统计
 select count(id) cnt,datepart(mm,time) [Month]
 from [table]
 where [time] between '2007/09/08 09:10:43' and '2007/10/09 04:32:37'
 group by datepart(mm,time)
 --按照日统计
 select count(id) cnt,datepart(dd,time) [Day]
 from [table]
 where time between '2007/09/08 09:10:43' and '2007/10/09 04:32:37'
 group by datepart(dd,time)
 --按照小时统计
 select count(id) cnt,datepart(hh,time) [Hour]
 from [table]
 where [time] between '2007/09/08 09:10:43' and '2007/10/09 04:32:37'
 group by datepart(hh,time)

sqlserver

 

按年的月份

declare @Year int
set @Year=2009
select 
    m as [Date],
    sum(
        case when datepart(month,AccessDateTime)=m 
        then   AccessCount else 0 end
       )  as [Count]  
from 
    Counter c,
    (
        select 1 m
        union all select 2
        union all select 3
        union all select 4
        union all select 5
        union all select 6
        union all select 7
        union all select 8
        union all select 9
        union all select 10
        union all select 11
        union all select 12
    ) aa
where 
    @Year=year(AccessDateTime) 
group by   
    m

按天的小时
declare @DateTime datetime set @DateTime=getdate()

select right(100+a,2)+ ':00 -> '+right(100+b,2)+ ':00 ' as DateSpan, sum( case when datepart(hour,AccessDateTime)> =a and datepart(hour,AccessDateTime) <b then AccessCount else 0 end ) as [Count] from Counter c , (select 0 a,1 b union all select 1,2 union all select 2,3 union all select 3,4 union all select 4,5 union all select 5,6 union all select 6,7 union all select 7,8 union all select 8,9 union all select 9,10 union all select 10,11 union all select 11,12 union all select 12,13 union all select 13,14 union all select 14,15 union all select 15,16 union all select 16,17 union all select 17,18 union all select 18,19 union all select 19,20 union all select 20,21 union all select 21,22 union all select 22,23 union all select 23,24 ) aa where datediff(day,@DateTime,AccessDateTime)=0 group by right(100+a,2)+ ':00 -> '+right(100+b,2)+ ':00 '