开发中用到的数据库查询案例

目录

1、查询过去12个月的数据，并统计每个月数据的数量

2、查询过去12个月的数据，并统计每个月数据的数量，如果某个月数据没有，也展示出为0

---

1、查询过去12个月的数据，并统计每个月数据的数量

select date_format(casetime,'%Y-%m') as month,count(casetime) as countCasecharge

from police_case where date_format(casetime,'%Y%m')>date_format(date_sub(curdate(),interval 12 month),'%Y-%m') group by date_format(casetime,'%Y-%m') order by date_format(casetime,'%Y-%m');

2、查询过去12个月的数据，并统计每个月数据的数量，如果某个月数据没有，也展示出为0

select d.month,count(pc.casetime) as count 
from(
select date_format((CURDATE() - INTERVAL 0 MONTH),'%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
)d
left join police_case pc on date_format(pc.casetime,'%Y-%m')=d.month 
group by month
order by date_format(casetime,'%Y-%m');