mysql 查询本月、本年的数据

最近策划给了博主一个这样的需求:

用户投放一次垃圾后，在数据库增加一条垃圾投放记录，并给它相应的积分。

然后。用户可以获取自己本月、本年的总积分(将每条记录的积分加起来)排名。

这里附上建表语句

CREATE TABLE `launch_info`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` int(10) NOT NULL COMMENT '用户id',
  `time` datetime(0) NOT NULL COMMENT '投放时间',
  `weight` double(10, 2) NOT NULL COMMENT '本次投放重量',
  `score` int(8) NOT NULL COMMENT '本次投放获取的积分',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


INSERT INTO `launch_info` VALUES (3, 1, '2019-05-29 20:01:01', 10.00, 100);
INSERT INTO `launch_info` VALUES (4, 1, '2019-05-29 20:05:22', 10.00, 100);
INSERT INTO `launch_info` VALUES (5, 2, '2019-05-12 10:18:20', 20.00, 200);
INSERT INTO `launch_info` VALUES (6, 3, '2019-05-13 10:18:38', 15.00, 10);
INSERT INTO `launch_info` VALUES (7, 2, '2018-05-12 10:12:12', 10.00, 123);
INSERT INTO `launch_info` VALUES (8, 3, '2019-05-30 22:29:29', 10.00, 50);
INSERT INTO `launch_info` VALUES (9, 4, '2019-05-02 21:16:50', 0.00, 0);
INSERT INTO `launch_info` VALUES (10, 3, '2019-04-05 22:38:36', 1.00, 1000);
INSERT INTO `launch_info` VALUES (11, 5, '2019-05-23 23:40:20', 1.00, -1);

 

好吧。问题要一个一个来解决。

第一个问题-------怎么根据时间来查询本月、本年的总积分呢

select userId,sum(weight) total_weight 
from launch_info where userId = 1
and DATE_FORMAT(time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')

附查询的sql语句如上所示，其实这条语句就是一个普通的查询语句，只是在后面加了一个 

and DATE_FORMAT(time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')

这里说明一下这条语句。DATE_FORMAT()函数，顾名思义，是一个将日期格式化的函数。然后这里的 CURDATE()是获取当前的时间，这条语句的意思就是将这条记录的时间和现在的时间同时按这个格式化规则进行格式化，如果结果相同，就代表了是本月的。

那么查询本年的数据也是一样的，只要把这条语句改成

and DATE_FORMAT(time,'%Y') = DATE_FORMAT(CURDATE(),'%Y')

或者是

YEAR(time)=YEAR(NOW())

就行了。

emmm，虽然策划是只要求博主做查询本月和本年的记录，但是万一以后又遇到要查本天的、半年之内的。。。。

所以，博主干脆一次性的做个记录把，也顺带分享给大家。

Let's go

查询本天

TO_DAYS(time) = TO_DAYS(NOW())

查询n天之前的

TO_DAYS(NOW()) - TO_DAYS(time) = ?

比如查昨天的 n 就等于1，查前天的n就等于2.（博主好像有点啰嗦）

查询前n天之内的(包括了今天)

DATE_SUB(CURDATE(), INTERVAL ? DAY) < date(time)

DATE_SUB() 函数的功能是从日期减去指定的时间间隔。

查询本月

DATE_FORMAT(time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')

查询第n个月前的

WHERE PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), DATE_FORMAT(time,'%Y%m')) = ?

PERIOD_DIFF(P1,P2)函数的功能是返回周期P1和P2之间的月数。 P1和P2格式为YYMM或YYYYMM

查询n个月之内的

DATE_SUB(CURDATE(), INTERVAL ? MONTH) < time

查询本年

DATE_FORMAT(time,'%Y') = DATE_FORMAT(CURDATE(),'%Y')
或
YEAR(time)=YEAR(NOW())

哎呀，太多了。博主已经不想再往下写了，等以后如果用到了博主再加上来把。

emmm，剩下的问题就是根据总分来对用户进行排名了。

具体操作请参见博主下一篇博客

https://blog.csdn.net/zhousw1999/article/details/89742059