MySQL在每月的某个星期有内置功能吗？
在MySQL中，没有标准功能可以每月获得一个星期。您需要使用以下语法-
SELECT WEEK(yourDateColumnName, 5) - WEEK(DATE_SUB(yourDateColumnName, INTERVAL DAYOFMONTH(yourDateColumnName) - 1 DAY), 5) + 1 AS anyAliasName FROM yourTableName;
为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table FirstWeekOfMonth -> ( -> Id int NOT NULL AUTO_INCREMENT primary key, -> yourdate date -> );
现在，您可以使用INSERT命令在表中插入一些记录。查询如下-
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-01-18');
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-04-19');
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-05-20');
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-12-31');
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-10-05');
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-08-25');使用select语句显示表中的所有记录。查询如下-
mysql> select *from FirstWeekOfMonth;
以下是输出-
+----+------------+ | Id | yourdate | +----+------------+ | 1 | 2019-01-18 | | 2 | 2019-04-19 | | 3 | 2019-05-20 | | 4 | 2019-12-31 | | 5 | 2019-10-05 | | 6 | 2019-08-25 | +----+------------+ 6 rows in set (0.00 sec)
这是获取月中星期几的查询-
mysql> SELECT WEEK(yourdate, 5) - -> WEEK(DATE_SUB(yourdate, INTERVAL DAYOFMONTH(yourdate) - 1 DAY), 5) + 1 as FirstWeekDemo from FirstWeekOfMonth;
以下是输出-
+---------------+ | FirstWeekDemo | +---------------+ | 3 | | 3 | | 4 | | 6 | | 1 | | 4 | +---------------+ 6 rows in set (0.00 sec)