dti70601
2015-07-19 18:13
浏览 94
已采纳

从月开始当前月份获取mysql表中的值总和

Hi i currently have two tables, one has daily entries and other gets the total of those and stores monthly values.

I'm able to get totals of each month and year with below query, but what i'd like to do is only get the months where month is greater than and equal to current month. btw date column is in date format (yyyy-mm-dd)

$revbyMonth = $conn->prepare("SELECT EXTRACT(MONTH FROM date) as month, EXTRACT(YEAR FROM date) as year, SUM(rev) as total FROM {$tempName} GROUP BY year, month"); 

图片转代码服务由CSDN问答提供 功能建议

您好我目前有两个表,一个包含每日条目,其他表获取总数,并存储月度值。< / p>

我可以通过以下查询获得每个月和每年的总数,但我想做的只是获得月份大于等于当月的月份。 btw日期列的日期格式为(yyyy-mm-dd)

  $ revbyMonth = $ conn-&gt; prepare(“SELECT EXTRACT(月份来自日期)作为月份,EXTRACT  (年份从日期开始)作为年份,SUM(rev)为总计FROM {$ tempName} GROUP BY年,月“);  
   
 
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dpno17028 2015-07-19 18:16
    已采纳

    You want to add a where clause immediately after the from clause. I think this will work for you. If the date has no time component:

    where date > date_sub(curdate(), interval day(curdate()) day)
    

    The expression date_sub(curdate(), interval day(curdate()) day) gets the last day of the previous month. If the dates have a time component:

    where date >= date_sub(curdate(), interval day(curdate()) - 1 day)
    

    should work.

    Note: this is better than other methods that process date using functions, such as:

    where year(date) > year(curdate()) or
          (year(date) = year(curdate()) and month(date) >= month(curdate()) )
    

    The use of the functions prevents MySQL from using an index on the date column.

    打赏 评论

相关推荐 更多相似问题