postgresql统计年月日
2019-04-13 12:58:37 来源：admin 点击：960
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)