posqgresql

--按年分组查看

   select  to_char(time_field, 'YYYY') as d ,  count(id)  as  total_count,sum (count_field)  as  total_amount from  table_name

  where  time_field  between  start_time  and  end_time group by d

--按月分组查看

   select  to_char(time_field, 'YYYY-MM') as d ,  count(id)  as  total_count,sum (count_field)   as  total_amount from  table_name

  where time_field between  start_time  and  end_time  group by d

--按天分组查看

   select  to_char(time_field, 'YYYY-MM-DD') as d ,  count(id)  as  total_count,sum (count_field)   as  total_amount from  table_name

  where time_field between  start_time  and  end_time  group by d

--按周统计

select 

row_time::DATE-(extract(dow from row_time::TIMESTAMP)-1||'day')::interval monday,

count(*) amount

from acd_details 

where 1=1 

GROUP BY row_time::DATE-(extract(dow from row_time::TIMESTAMP)-1||'day')::interval

--按小时分组查看

     select  to_char(time_field, 'YYYY-MM-DD  HH24 ' ) as d ,  count(id)  as  total_count,sum (count_field)   as  total_amount from  table_name

  where time_field  between start_time  and  end_time  group by d  order  by  d

--按秒分组查看

     select  to_char(time_field, 'YYYY-MM-DD  HH24:MI:SS ' ) as d ,  count(id)  as  total_countl,sum (count_field)   as  total_amount from  table_name

  where  time_field  between start_time  and  end_time  group by d

mysql

12 统计每年每月每日

1、每年

select year(ordertime) AS '年',

sum(Total) '销售合计'

from order_list

group by year(ordertime)

2、每月

select year(ordertime) '年',

month(ordertime) '月',

sum(Total) '销售合计'

from order_list

group by year(ordertime),

month(ordertime)

3、每日

select year(ordertime) '年',

month(ordertime) '月',

day(ordertime) '日',

sum(Total) '销售合计'

from order_list

group by year(ordertime),

month(ordertime),

day(ordertime)

另外每日也可以这样:

select convert(char(8),ordertime,112) dt,

sum(Total) '销售合计'

from order_list

group by convert(char(8),ordertime,112) 

另外，每月(年、日)的记录条数

select year(ordertime) '年',

month(ordertime) '月',

count(*) '销售记录'

from order_list

group by year(ordertime),

month(ordertime)