其实可以用存储过程，但想用另一种方法实现：
首先创建一个辅助表，可以设置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.