— 按年月日统计
SELECT DATE(trade_time),site_link,COUNT(*) ,SUM(amount_year) FROM zc_tb_site_hiss GROUP BY DATE(trade_time),site_link ORDER BY trade_time DESC;

— 按年月统计
SELECT YEAR(trade_time),MONTH(trade_time),site_link,COUNT(*) ,SUM(amount_year)
FROM zc_tb_site_hiss GROUP BY YEAR(trade_time),MONTH(trade_time),site_link ORDER BY trade_time DESC ;

— 按年统计
SELECT YEAR(trade_time),site_link,COUNT(*) ,SUM(amount_year) FROM zc_tb_site_hiss GROUP BY YEAR(trade_time),site_link ORDER BY trade_time DESC;

MySQL里获取当前week、month、quarter的start_date/end_date
当前week的第一天：select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY)
当前week的最后一天：elect date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY)
前一week的第一天：select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 8 DAY)
前一week的最后一天：select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 2 DAY)
前两week的第一天：select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 15 DAY)
前两week的最后一天：select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 9 DAY)
当前month的第一天：SELECT concat(date_format(LAST_DAY(now()),’%Y-%m-‘),’01’)
当前month的最后一天：
SELECT LAST_DAY(now())
前一month的第一天：
SELECT concat(date_format(LAST_DAY(now() - interval 1 month),’%Y-%m-‘),’01’)
前一month的最后一天：
SELECT LAST_DAY(now() - interval 1 month)
前两month的第一天：
SELECT concat(date_format(LAST_DAY(now() - interval 2 month),’%Y-%m-‘),’01’)
前两month的最后一天：SELECT LAST_DAY(now() - interval 2 month)
当前quarter的第一天：
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())3-3 month),’%Y-%m-‘),’01’)
当前quarter的最后一天：
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())3-1 month)
前一quarter的第一天：
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interv
— 查询下一个月的统计
select from pj_tb_pay where month(end_time) = month(DATE_ADD(curdate(),INTERVAL 1 MONTH)) and year(end_time) = year(DATE_ADD(curdate(),INTERVAL 1 MONTH));
— 查询当前月的统计
select from pj_tb_pay where 1=1 and month(f.trade_time) = month(curdate()) and year(f.trade_time) = year(curdate());
— 查询上一个月统计 select from pj_tb_pay where month(end_time) = month(DATE_SUB(curdate(),INTERVAL 1 MONTH)) and year(end_time) = year(DATE_SUB(curdate(),INTERVAL 1 MONTH));
— 计划下月收款 select IFNULL(sum(plan_money),0)plan_money from pj_tb_fee where emc_id=? and month(trade_time) = month(DATE_ADD(curdate(),INTERVAL 1 MONTH)) and year(trade_time) = year(DATE_ADD(curdate(),INTERVAL 1 MONTH));
— 计划下月付款 select IFNULL(sum(plan_amount),0)PLAN_AMOUNT from pj_tb_pay where ht_id=? and month(end_time) = month(DATE_ADD(curdate(),INTERVAL 1 MONTH)) and year(end_time) = year(DATE_ADD(curdate(),INTERVAL 1 MONTH));
一、年度查询
查询 本年度的数据
SELECT FROM blog_article WHERE year( FROM_UNIXTIME( BlogCreateTime ) ) = year( curdate( ))
二、查询季度数据
查询数据附带季度数
SELECT ArticleId, quarter( FROM_UNIXTIME( BlogCreateTime ) ) FROM blog_article
其他的同前面部分：查询 本季度的数据
SELECT FROM blog_article WHERE quarter( FROM_UNIXTIME( BlogCreateTime ) ) = quarter( curdate( ))
三、查询月度数据
本月统计(MySQL)
select from booking where month(booking_time) = month(curdate()) and year(booking_time) = year(curdate())
本周统计(MySQL)
select from spf_booking where month(booking_time) = month(curdate()) and week(booking_time) = week(curdate())
四、时间段
N天内记录 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段) <= N
当天的记录 where date(时间字段)=date(now()) 或 where to_days(时间字段) = to_days(now());
查询一周： select from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);
查询一个月：
select from table where DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(column_time);
查询’06-03’到’07-08’这个时间段内所有过生日的会员： Select From user Where DATE_FORMAT(birthday,’%m-%d’) >= ‘06-03’ and DATE_FORMAT(birthday,’%m-%d’) <= ‘07-08’;
统计一季度数据，表时间字段为：savetime
group by concat(date_format(savetime, ‘%Y ‘),FLOOR((date_format(savetime, ‘%m ‘)+2)/3))
或 select YEAR(savetime)10+((MONTH(savetime)-1) DIV 3) +1,count() from yourTable
group by YEAR(savetime)*10+((MONTH(savetime)-1) DIV 3) +1;

查询本周记录
select from ht_invoice_information where WEEKOFYEAR(create_date)=WEEKOFYEAR(NOW());
#查询上周记录
select from ht_invoice_information where create_date>=date_add(now(),interval -(8 + weekday(now())) day)
and create_date<=date_add(now(),interval -(1 + weekday(now())) day);
#或者
select from ht_invoice_information where WEEKOFYEAR(create_date)=WEEKOFYEAR(DATE_SUB(now(),INTERVAL 1 week));
#查询本月数据
select from ht_invoice_information where MONTH(create_date)=MONTH(NOW()) and year(create_date)=year(now());
#查询上月数据
select from ht_invoice_information where create_date<=last_day(date_add(now(),interval -1 MONTH))
and create_date>=DATE_FORMAT(concat(extract(year_month from date_add(now(),interval -1 MONTH)),’01’),’%Y-%m-%d’);
#或者
select from ht_invoice_information where MONTH(create_date)=MONTH(DATE_SUB(NOW(),interval 1 month))
and year(create_date)=year(now());
#查询本季度数据
select from ht_invoice_information where QUARTER(create_date)=QUARTER(now());
#查询上季度数据
select from ht_invoice_information where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
#查询本年数据
select from ht_invoice_information where YEAR(create_date)=YEAR(NOW());
#查询上年数据
select from ht_invoice_information where year(create_date)=year(date_sub(now(),interval 1 year));