现有表如下
accessid createdate48 2010-11-01
48 2010-11-01
48 2010-11-01
...
48 2010-11-01期待SQL执行后效果accessid createdate48 2010-11-01
48 2010-11-02
48 2010-11-03
...
48 2010-11-30
48 2010-12-01---------------------------------------
另外不知道为什么执行declare就提示错误,开始以为是使用phpmyadmin的原因,后来直接命令行操作也还是不行,请大侠指点,分数有限了,请谅解。
accessid createdate48 2010-11-01
48 2010-11-01
48 2010-11-01
...
48 2010-11-01期待SQL执行后效果accessid createdate48 2010-11-01
48 2010-11-02
48 2010-11-03
...
48 2010-11-30
48 2010-12-01---------------------------------------
另外不知道为什么执行declare就提示错误,开始以为是使用phpmyadmin的原因,后来直接命令行操作也还是不行,请大侠指点,分数有限了,请谅解。
set @I=0;
select accessid ,DATE_ADD(`createdate`, INTERVAL @I:=@I+1 day) from aaa;用户自定义变量。
加自增主键列idx多好
select now()+interval idx day from tb
+----------+------------+
| accessid | createdate |
+----------+------------+
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
+----------+------------+
8 rows in set (0.00 sec)mysql> set @a=0;
Query OK, 0 rows affected (0.01 sec)mysql> select accessid, adddate(createdate,interval @a:=@a+1 day) from test;
+----------+-------------------------------------------+
| accessid | adddate(createdate,interval @a:=@a+1 day) |
+----------+-------------------------------------------+
| 48 | 2010-11-02 |
| 48 | 2010-11-03 |
| 48 | 2010-11-04 |
| 48 | 2010-11-05 |
| 48 | 2010-11-06 |
| 48 | 2010-11-07 |
| 48 | 2010-11-08 |
| 48 | 2010-11-09 |
+----------+-------------------------------------------+
8 rows in set (0.00 sec)code]
可能我表诉的不太好,不是需要取出的结果是什么,而是直接希望改变数据库表中的数据......现在按照zuoxingyu和vipper23大侠的做法,打印结果是正确的,只是数据没变。另外能帮忙解答一下为什么declare提示错误吗?分有点少,不好意思了
+----------+------------+
| accessid | createdate |
+----------+------------+
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
| 48 | 2010-11-01 |
+----------+------------+
18 rows in set (0.00 sec)mysql> set @a=-1;
Query OK, 0 rows affected (0.00 sec)mysql> update tb set createdate=adddate(createdate,interval @a:=@a+1 day);
Query OK, 17 rows affected (0.00 sec)
Rows matched: 18 Changed: 17 Warnings: 0mysql> select * from tb;
+----------+------------+
| accessid | createdate |
+----------+------------+
| 48 | 2010-11-01 |
| 48 | 2010-11-02 |
| 48 | 2010-11-03 |
| 48 | 2010-11-04 |
| 48 | 2010-11-05 |
| 48 | 2010-11-06 |
| 48 | 2010-11-07 |
| 48 | 2010-11-08 |
| 48 | 2010-11-09 |
| 48 | 2010-11-10 |
| 48 | 2010-11-11 |
| 48 | 2010-11-12 |
| 48 | 2010-11-13 |
| 48 | 2010-11-14 |
| 48 | 2010-11-15 |
| 48 | 2010-11-16 |
| 48 | 2010-11-17 |
| 48 | 2010-11-18 |
+----------+------------+
18 rows in set (0.00 sec)
用变量累加即可
set @I=0;
select accessid ,DATE_ADD(`createdate`, INTERVAL @I:=@I+1 day) from aaa;