mysql 输出当前月所有日期与对应的星期

其实可以用存储过程，但想用另一种方法实现：

首先创建一个辅助表，可以设置CREATE TABLE `t4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

insert into t4(num) select id from xxx limit 31;(偷懒插入31条数据)

alter table t4 drop column num;

 

mysql> select * from t4;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |
| 28 |
| 29 |
| 30 |
| 31 |
+----+
31 rows in set (0.00 sec)

 

 

mysql> set @a=-1; 
Query OK, 0 rows affected (0.00 sec)

mysql>  select @a:=@a+1,DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL @a DAY)  AS '日期',DAYNAME(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL @a DAY) ) as '星期' from t4 where id<=DAYOFMONTH(last_day(curdate())); 
+----------+------------+-----------+
| @a:=@a+1 | 日期       | 星期      |
+----------+------------+-----------+
|        0 | 2015-08-01 | Saturday  |
|        1 | 2015-08-02 | Sunday    |
|        2 | 2015-08-03 | Monday    |
|        3 | 2015-08-04 | Tuesday   |
|        4 | 2015-08-05 | Wednesday |
|        5 | 2015-08-06 | Thursday  |
|        6 | 2015-08-07 | Friday    |
|        7 | 2015-08-08 | Saturday  |
|        8 | 2015-08-09 | Sunday    |
|        9 | 2015-08-10 | Monday    |
|       10 | 2015-08-11 | Tuesday   |
|       11 | 2015-08-12 | Wednesday |
|       12 | 2015-08-13 | Thursday  |
|       13 | 2015-08-14 | Friday    |
|       14 | 2015-08-15 | Saturday  |
|       15 | 2015-08-16 | Sunday    |
|       16 | 2015-08-17 | Monday    |
|       17 | 2015-08-18 | Tuesday   |
|       18 | 2015-08-19 | Wednesday |
|       19 | 2015-08-20 | Thursday  |
|       20 | 2015-08-21 | Friday    |
|       21 | 2015-08-22 | Saturday  |
|       22 | 2015-08-23 | Sunday    |
|       23 | 2015-08-24 | Monday    |
|       24 | 2015-08-25 | Tuesday   |
|       25 | 2015-08-26 | Wednesday |
|       26 | 2015-08-27 | Thursday  |
|       27 | 2015-08-28 | Friday    |
|       28 | 2015-08-29 | Saturday  |
|       29 | 2015-08-30 | Sunday    |
|       30 | 2015-08-31 | Monday    |
+----------+------------+-----------+
31 rows in set (0.00 sec)

 

 

mysql>  set @a=-1;  
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:=@a+1,DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL @a DAY)  AS '日期',WEEKDAY(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL @a DAY) )+1 as '星期' from t4 where id<=DAYOFMONTH(last_day(curdate())); 
+----------+------------+--------+
| @a:=@a+1 | 日期       | 星期   |
+----------+------------+--------+
|        0 | 2015-08-01 |      6 |
|        1 | 2015-08-02 |      7 |
|        2 | 2015-08-03 |      1 |
|        3 | 2015-08-04 |      2 |
|        4 | 2015-08-05 |      3 |
|        5 | 2015-08-06 |      4 |
|        6 | 2015-08-07 |      5 |
|        7 | 2015-08-08 |      6 |
|        8 | 2015-08-09 |      7 |
|        9 | 2015-08-10 |      1 |
|       10 | 2015-08-11 |      2 |
|       11 | 2015-08-12 |      3 |
|       12 | 2015-08-13 |      4 |
|       13 | 2015-08-14 |      5 |
|       14 | 2015-08-15 |      6 |
|       15 | 2015-08-16 |      7 |
|       16 | 2015-08-17 |      1 |
|       17 | 2015-08-18 |      2 |
|       18 | 2015-08-19 |      3 |
|       19 | 2015-08-20 |      4 |
|       20 | 2015-08-21 |      5 |
|       21 | 2015-08-22 |      6 |
|       22 | 2015-08-23 |      7 |
|       23 | 2015-08-24 |      1 |
|       24 | 2015-08-25 |      2 |
|       25 | 2015-08-26 |      3 |
|       26 | 2015-08-27 |      4 |
|       27 | 2015-08-28 |      5 |
|       28 | 2015-08-29 |      6 |
|       29 | 2015-08-30 |      7 |
|       30 | 2015-08-31 |      1 |
+----------+------------+--------+
31 rows in set (0.00 sec)

 

 

mysql> set @a=-1;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> select @a:=@a+1,DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL @a DAY)  AS '日期',DAYOFWEEK(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL @a DAY) ) as '星期' from t4 where id<=DAYOFMONTH(last_day(curdate()));
+----------+------------+--------+
| @a:=@a+1 | 日期       | 星期   |
+----------+------------+--------+
|        0 | 2015-08-01 |      7 |
|        1 | 2015-08-02 |      1 |
|        2 | 2015-08-03 |      2 |
|        3 | 2015-08-04 |      3 |
|        4 | 2015-08-05 |      4 |
|        5 | 2015-08-06 |      5 |
|        6 | 2015-08-07 |      6 |
|        7 | 2015-08-08 |      7 |
|        8 | 2015-08-09 |      1 |
|        9 | 2015-08-10 |      2 |
|       10 | 2015-08-11 |      3 |
|       11 | 2015-08-12 |      4 |
|       12 | 2015-08-13 |      5 |
|       13 | 2015-08-14 |      6 |
|       14 | 2015-08-15 |      7 |
|       15 | 2015-08-16 |      1 |
|       16 | 2015-08-17 |      2 |
|       17 | 2015-08-18 |      3 |
|       18 | 2015-08-19 |      4 |
|       19 | 2015-08-20 |      5 |
|       20 | 2015-08-21 |      6 |
|       21 | 2015-08-22 |      7 |
|       22 | 2015-08-23 |      1 |
|       23 | 2015-08-24 |      2 |
|       24 | 2015-08-25 |      3 |
|       25 | 2015-08-26 |      4 |
|       26 | 2015-08-27 |      5 |
|       27 | 2015-08-28 |      6 |
|       28 | 2015-08-29 |      7 |
|       29 | 2015-08-30 |      1 |
|       30 | 2015-08-31 |      2 |
+----------+------------+--------+
31 rows in set (0.00 sec)

 

注意:最后一个输出貌似是所有星期都加上1了,感觉不对,其实这是函数的规定来的:

 

DAYOFWEEK(date)
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。
mysql> select DAYOFWEEK('1998-02-03');

WEEKDAY(date)
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
mysql> select WEEKDAY('1997-10-04 22:23:00');
-> 5

 

这个例子用到的一个技巧是第一个@a:=@a+1来模拟循环记数，伪rownum.