如何使用 MySQL 从新闻表中选择过去 6 个月
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8941377/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select last 6 months from news table using MySQL
提问by Zabs
I am trying to select the last 6 months of entries in a table, I have a column called datetime and this is in a datetime mysql format.
我正在尝试选择表中最近 6 个月的条目，我有一个名为 datetime 的列，这是 datetime mysql 格式。
I have seen many ways using interval and other methods - which method should I use? Thanks
我见过很多使用间隔和其他方法的方法——我应该使用哪种方法？谢谢
回答by Pablo Santa Cruz
Try this:
尝试这个：
select *
from table
where your_dt_field >= date_sub(now(), interval 6 month);
Query reads: give me all entries in tablewhere the field corresponding to the entry date is newer than 6 months.
查询内容：给我所有条目，table其中条目日期对应的字段比 6 个月新。
回答by Bufaroosha
I tried @user319198 answer to display last 6 months (sum of) sales, it worked but I faced one issue in the oldest month, i do not get the sales amount of the whole month. The result starts from the equivalent current day of that month.
我试过@user319198 的答案来显示过去 6 个月（总和）的销售额，它起作用了，但我在最旧的月份遇到了一个问题，我没有得到整个月的销售额。结果从该月的等效当天开始。
Just I want to share my solution if any one interested:-
如果有人感兴趣，我想分享我的解决方案：-
yourdate_column > DATE_SUB(now(), INTERVAL 7 MONTH)
limit 6
Also it will be great if anyone has better solution for my case J.
如果有人对我的案例 J 有更好的解决方案，那也会很棒。
回答by stamster
To me, this looks like a solution as I'm using it with MariaDB, take a look at WHERE clause:
对我来说，这看起来像一个解决方案，因为我将它与 MariaDB 一起使用，看看 WHERE 子句：
SELECT MONTH(yourTimestampOrDateColumn) AS MONTH, USER, ActionID, COUNT(*) AS TOTAL_ACTIONS, ROUND(SUM(totalpoints)) AS TOTAL_PTS
FROM MyTable
WHERE MONTH(yourTimestampOrDateColumn) BETWEEN MONTH(CURDATE() - INTERVAL 6 MONTH) AND MONTH(CURDATE())
GROUP BY MONTH;
On the image we see only months where user had actual data recorded in a DB (thus showing only 4 months instead of 6).
在图像上，我们只看到用户在数据库中记录了实际数据的月份（因此只显示了 4 个月而不是 6 个月）。
So this month is 10th (October), 6 months ago was 4th month (April), thus query will look for that interval (from 4 to 10).
所以这个月是 10 号（10 月），6 个月前是第 4 个月（4 月），因此查询将查找那个间隔（从 4 到 10）。
回答by Md. Mahmud Hasan
You can also use TIMESTAMPDIFF
您还可以使用 TIMESTAMPDIFF
TIMESTAMPDIFF(MONTH, your_date_column, now()) <= 6 )
回答by Haritsinh Gohil
You can get last six month's data by subtracting interval of 6 monthfrom CURDATE()(CURDATE()is MySQL function which returns Today's date).
您可以通过interval of 6 month从CURDATE()( CURDATE()is MySQL function which returns Today's date) 中减去来获得过去六个月的数据。
SELECT * FROM table
WHERE your_date_field >= CURDATE() - INTERVAL 6 MONTH;
Or you can use BETWEENoperator of MySQL as Below:
或者您可以使用BETWEENMySQL 的运算符如下：
SELECT * FROM table
WHERE your_date_field BETWEEN CURDATE() - INTERVAL 6 MONTH AND CURDATE();
