请教MySQL中如何判断某一天是当月的第几周啊？

Question

请教MySQL中如何判断某一天是当月的第几周啊？如，2011-04-03是今年4月份的第1周，2011-04-04是今年4月份的第2周，这个怎么在MySQL中进行计算啊。谢谢。

Answer 1

set @d='2011-04-01';
select (day(@d)+WEEKDAY(@d-interval day(@d) day)) div 7 + 1;
mysql> set @d='2011-04-03';
Query OK, 0 rows affected (0.00 sec)mysql> select (day(@d)+WEEKDAY(@d-interval day(@d) day)) div 7 + 1;
+------------------------------------------------------+
| (day(@d)+WEEKDAY(@d-interval day(@d) day)) div 7 + 1 |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+
1 row in set (0.00 sec)mysql> set @d='2011-04-04';
Query OK, 0 rows affected (0.00 sec)mysql> select (day(@d)+WEEKDAY(@d-interval day(@d) day)) div 7 + 1;
+------------------------------------------------------+
| (day(@d)+WEEKDAY(@d-interval day(@d) day)) div 7 + 1 |
+------------------------------------------------------+
|                                                    2 |
+------------------------------------------------------+
1 row in set (0.00 sec)mysql> set @d='2011-04-01';
Query OK, 0 rows affected (0.00 sec)mysql> select (day(@d)+WEEKDAY(@d-interval day(@d) day)) div 7 + 1;
+------------------------------------------------------+
| (day(@d)+WEEKDAY(@d-interval day(@d) day)) div 7 + 1 |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+
1 row in set (0.00 sec)mysql>

Answer 2

set @d='2011-04-03';
SELECT WEEK(@dd,7)-WEEK(DATE(CONCAT(YEAR(@dd),'-',MONTH(@dd),'-01')),7)+
IF(WEEK(@dd,7)=WEEK(DATE(CONCAT(YEAR(@dd),'-',MONTH(@dd),'-01')),7),1,0);

Answer 3

最好在程序里计算，mysql只是数据库，用来存储和查询数据的，不要做逻辑运算，会影响性能和索引的使用

Answer 4

set @d='2011-4-3';
select weekofyear(@d)-weekofyear(@d-interval day(@d)-1 day)+1;mysql> set @d='2011-4-3';
Query OK, 0 rows affected (0.00 sec)mysql> select weekofyear(@d)-weekofyear(@d-interval day(@d)-1 day)+1;
+--------------------------------------------------------+
| weekofyear(@d)-weekofyear(@d-interval day(@d)-1 day)+1 |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+
1 row in set (0.00 sec)mysql> set @d = '2011-4-4';
Query OK, 0 rows affected (0.00 sec)mysql> select weekofyear(@d)-weekofyear(@d-interval day(@d)-1 day)+1;
+--------------------------------------------------------+
| weekofyear(@d)-weekofyear(@d-interval day(@d)-1 day)+1 |
+--------------------------------------------------------+
|                                                      2 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Answer 5

mysql> select week(@d)-week(@d-interval day(@d)-1 day);
    -> //

Answer 6

MYSQL就有这样的时间函数。week（）

Answer 7

mysql> set @d='2011-4-4';
Query OK, 0 rows affected (0.00 sec)mysql> select week(@d)-week(@d-interval day(@d)-1 day)+1;
+--------------------------------------------+
| week(@d)-week(@d-interval day(@d)-1 day)+1 |
+--------------------------------------------+
|                                          2 |
+--------------------------------------------+
1 row in set (0.00 sec)