按年份、月份查询SQL语句

# 第一个是用月份的字段来显示
SELECT
	DATE_FORMAT( r.assess_time, '%M' ) MONTH,
	AVG( r.courage ) courage,
	AVG( r.take ) take,
	AVG( r.possess ) possess,
	AVG( r.focus ) focus ,
	AVG( r.type ) type 
FROM
	assess_record r 
WHERE
	r.dog_id = 403
GROUP BY
	DATE_FORMAT( r.assess_time, '%M' )
	
# 第二个是用年份的字段来显示
SELECT
	DATE_FORMAT( r.exam_time, '%Y' ) YEAR,
	AVG( r.physical_total ) physicalTotal,
	AVG( r.police_total ) policeTotal,
	AVG( r.reproduction_total ) reproductionTotal,
	AVG( r.descendant_total ) descendantTotal,
	AVG( r.score ) score 
FROM
	exam_record r 
WHERE
	r.dog_id = 348
GROUP BY
	DATE_FORMAT( r.exam_time, '%Y' )

相关博客