[数据库] MYSQL之SQL查询(日期时间篇)

CASE:查询当天/昨天数据

# 当天
select * from table where to_days(时间字段) = to_days(now());

# 昨天
select * from table where to_days(now( ) ) - to_days( 时间字段名) <= 1

CASE:查询近N分钟/周/月/年的数据

# 近5分钟
SELECT * FROM table WHERE 时间字段 >= DATE_SUB(now(),INTERVAL 5 MINUTE)

# 近7天
SELECT * FROM table WHERE date(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 7 DAY) //查询近七天

# 近一月
SELECT * FROM table WHERE date(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 1 MONTH)//查询近一月

# 近一年
SELECT * FROM table WHERE date(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 1 YEAR) //查询近一年

CASE:查询本周/月/年数据

# 本周数据（周一为第一天）
SELECT * FROM table WHERE YEARWEEK(date_format(时间字段,'%Y-%m-%d'), 1) = YEARWEEK(now(),1);

# 本月
select * from table where DATE_FORMAT(时间字段, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

# 本年
select * from table where YEAR(时间字段)=YEAR(now());

CASE:查询上月/年的数据

# 上一月
select * from table where PERIOD_DIFF(date_format(now(),'%Y%m') , date_format(时间字段,'%Y%m')) =1

# 上一年
select * from table where year(时间字段)=year(date_sub(now(),interval 1 year));

CASE:查询某年度的每个月数据报表

select 
	a.date AS 'xData'
	, IFNULL(b.sum, 0) AS 'yData'
from  (
	select 
		DATE_FORMAT(
			adddate(DATE_SUB(CURDATE(), INTERVAL dayofyear(now()) - 1 DAY),
			INTERVAL numlist.id - 1 month), '%m'
		) as date
	from (
		SELECT 
			@xi := @xi + 1 as id
		from (
			SELECT 1 
			UNION 
			SELECT 2 
			UNION 
			SELECT 3
		) xc1
		, (
			SELECT 1 
			UNION 
			SELECT 2 
			UNION SELECT 3 
			UNION SELECT 4
		) xc2
		, (SELECT @xi := 0) xc0
	) as numlist
) a
left join(
	SELECT  
		IFNULL(SUM(income),0) sum
		, DATE_FORMAT(time, '%m') as date
	FROM zq_cnz_hy_income_report ts
	WHERE YEAR(time)=#{year}
	GROUP BY date 
	ORDER BY date
) b
on a.date = b.date 
order by a.date

CASE:查询近一年的每个月数据报表

SELECT v.month AS 'xData',IFNULL(b.COUNT,0) AS 'yData' FROM (
	SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`
) v
LEFT JOIN(
	SELECT 
		LEFT(a.time,7) AS 'month',SUM(income) AS COUNT
	FROM zq_cnz_hy_income_report AS a
	LEFT JOIN zq_cnz_hy_user_station zs ON a.station_id=zs.station_id
	WHERE DATE_FORMAT(a.time,'%Y-%m')>DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH),'%Y-%m')
	GROUP BY MONTH
) AS b
ON v.month = b.month
GROUP BY v.month 
ORDER BY v.month

CASE:查询本月数据报表

select 
	a.date as 'xData'
	, IFNULL(b.sum, 0) AS 'yData'
from  (
	select 
		date 
	from (
		SELECT 
			DATE_FORMAT(DATE_SUB(last_day(curdate()), INTERVAL xc-1 day), '%Y-%m-%d') as date
		FROM (
			SELECT 
				@xi:=@xi+1 as xc 
			from 
				(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1,
				(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,
				(SELECT @xi:=0) xc0
		) xcxc
	) x0 
	where x0.date >= (select date_add(curdate(),interval-day(curdate())+1 day))
) a 
left join (
	select 
		IFNULL(SUM(income),0) as sum
		, DATE_FORMAT(time,'%Y-%m-%d') as date
	FROM zq_cnz_hy_income_report ts
	group by DATE_FORMAT(time, '%Y-%m-%d')
) b
on a.date =b.date 
order by a.date

CASE:查询近一个月数据报表

SELECT
	b.created AS 'xData'
	, IFNULL(c.sum, 0) AS 'yData'
FROM (
	SELECT
		@cdate := date_add( @cdate, INTERVAL - 1 DAY ) created
	FROM( 
		SELECT 
			@cdate := date_add( CURDATE( ), INTERVAL 1 DAY ) 
		FROM zq_cnz_hy_income_report 
		LIMIT 30 
	) a
) b
LEFT JOIN (
	select 
		SUM(income) as sum
		, DATE_FORMAT(time,'%Y-%m-%d') as date
	FROM zq_cnz_hy_income_report ts
	group by DATE_FORMAT(time, '%Y-%m-%d')
) c ON b.created = date_format( c.date, '%Y-%m-%d')
GROUP BY b.created
ORDER BY b.created;