MySQL 中是否有用于获取月份周数的内置函数?
mysqlmysqli database
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 -> ); Query OK, 0 rows affected (2.50 sec)
现在，您可以使用 insert 命令在表中插入一些记录。查询如下 −
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-01-18');
Query OK, 1 row affected (0.20 sec)
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-04-19');
Query OK, 1 row affected (0.19 sec)
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-05-20');
Query OK, 1 row affected (0.10 sec)
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-12-31');
Query OK, 1 row affected (0.19 sec)
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-10-05');
Query OK, 1 row affected (0.12 sec)
mysql> insert into FirstWeekOfMonth(yourdate) values('2019-08-25');
Query OK, 1 row affected (0.16 sec)
使用 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)
