年龄、等级可以用IF。CASE WHEN解决,
update tt set 会员=if(会员='金卡',1,if(会员='银卡',2,if(会员='普通卡',3,4))地址不好处理,格式要固定,判断是否有省、市、区,如果有,则取
比如市:
省以后,市以前的内容, 否则取NULL
update tt set 会员=if(会员='金卡',1,if(会员='银卡',2,if(会员='普通卡',3,4))地址不好处理,格式要固定,判断是否有省、市、区,如果有,则取
比如市:
省以后,市以前的内容, 否则取NULL
+--------+
| m |
+--------+
| 金卡 |
| 银卡 |
| 普通卡 |
| 非VIP |
+--------+
4 rows in set (0.00 sec)mysql> update t set m=
-> case m
-> when '金卡' then 1
-> when '银卡' then 2
-> when '普通卡' then 3
-> else 4
-> end ;
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql> select * from t;
+------+
| m |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
if(年龄 between 18 and 25,2,
if(年龄 between 25 and 35,3,
if(年龄>35,4,1)))
mysql> select * from t;
+--------------------+
| area |
+--------------------+
| 湖南省长沙市岳麓区 |
| 广东省深圳市福田区 |
+--------------------+
2 rows in set (0.00 sec)mysql> update t set area=
-> concat(left(area,instr(area,'省')),
-> right(area,char_length(area)-instr(area,'市')));
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0mysql> select * from t;
+--------------+
| area |
+--------------+
| 湖南省岳麓区 |
| 广东省福田区 |
+--------------+
2 rows in set (0.00 sec)
mysql> select * from t;
+------+
| age |
+------+
| 16 |
| 20 |
| 21 |
| 30 |
| 33 |
| 50 |
| 45 |
+------+
7 rows in set (0.00 sec)mysql> update t set age = interval(age,18,25,35);
Query OK, 7 rows affected (0.03 sec)
Rows matched: 7 Changed: 7 Warnings: 0mysql> select * from t;
+------+
| age |
+------+
| 0 |
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
+------+
7 rows in set (0.00 sec)mysql> update t set age=age+1;
Query OK, 7 rows affected (0.02 sec)
Rows matched: 7 Changed: 7 Warnings: 0mysql> select * from t;
+------+
| age |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| 4 |
+------+
7 rows in set (0.00 sec)
+------+
| age |
+------+
| 16 |
| 20 |
| 21 |
| 30 |
| 33 |
| 50 |
| 45 |
+------+
7 rows in set (0.00 sec)mysql> update t set age = interval(age,0,18,25,35);
Query OK, 7 rows affected (0.02 sec)
Rows matched: 7 Changed: 7 Warnings: 0mysql> select * from t;
+------+
| age |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| 4 |
+------+
7 rows in set (0.00 sec)
#8楼 的代码 不能满足要求吗?使用mid 函数取出字符串中的年,月,日。
你先试着写一下,如果不行,把你自己写的代码贴出来大家来看。关键是自己先动手试一下。