在MySQL中使用INTERVAL和CURDATE [英] Working with INTERVAL and CURDATE in MySQL

问题描述

这是我第一个正在工作的请求：

  SELECT s.GSP_nom AS nom，timestamp，AVG（v.vote + v.prix）/ 2 AS avg 
 FROM votes_serveur AS v 
 INNER JOIN serveur AS s ON v.idServ = s.idServ 
 WHERE s.valide = 1 
 AND v.date> CURDATE（）-30 
 GROUP BY s.GSP_nom 
 ORDER BY avg DESC 
  

但是，在我的情况下，我要写12个前几个月收到的数据请求，有没有办法避免写：

  //上一个月的例子
 AND v.date> CURDATE（）-60 
 AND v.date< CURDATE（）-30 
  

我听说过INTERVAL，我去了MySQL文档，但我没有

使用INTERVAL的任何示例？

解决方案

p>您需要 DATE_ADD / DATE_SUB ：

  AND v.date> （DATE_SUB（CURDATE（），INTERVAL 2 MONTH））
 AND v.date< （DATE_SUB（CURDATE（），INTERVAL 1 MONTH））
  

应该工作。 $ b

I'm building a chart and I want to receive data for each month.

Here's my first request which is working:

SELECT s.GSP_nom AS nom, timestamp, AVG( v.vote +  v.prix  ) /2 AS avg
FROM votes_serveur AS v
INNER JOIN serveur AS s ON v.idServ = s.idServ
WHERE s.valide =1
AND v.date > CURDATE() -30
GROUP BY s.GSP_nom
ORDER BY avg DESC

But, in my case I've to write 12 request to receive data for the 12 previous months, is there any trick to avoid writing:

//  example for the previous month
 AND v.date > CURDATE() -60
AND v.date < CURDATE () -30

I heard about INTERVAL, I went to the MySQL doc but i didn't manage to implement it.

Any example of using INTERVAL please?

解决方案

You need DATE_ADD/DATE_SUB:

AND v.date > (DATE_SUB(CURDATE(), INTERVAL 2 MONTH))
AND v.date < (DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

should work.

这篇关于在MySQL中使用INTERVAL和CURDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！