有一个user表如下:
user_id | user_file_path
------------------------------
1 acc_10/file_63
2 acc_499/file_64
3 acc_500/file_64
4 acc_1201/file_64
现在要更新数据库的 user_file_path字段,规则是取acc_后面和/之间的数字(即10,499,500,1201)
得到这些数字后除500 得到的整数值
10/500=0,499/500=0,500/500=1,1201/500=2
得到0,0,1,2加/ 更新原来的行
即更新如下:user_id | user_file_path
------------------------------
1 0/acc_10/file_63
2 0/acc_499/file_64
3 1/acc_500/file_64
4 2/acc_1201/file_64问:如果用SQL 语句实现这功能?数据库用的postgresql.
请指教。
+---------+------------------+
| user_id | user_file_path |
+---------+------------------+
| 1 | acc_10/file_63 |
| 2 | acc_499/file_64 |
| 3 | acc_500/file_64 |
| 4 | acc_1201/file_64 |
+---------+------------------+
4 rows in set (0.00 sec)mysql> select concat(substr(user_file_path,5,instr(user_file_path,'/')-5) div 50
0,'/',user_file_path) as k from user;
+--------------------+
| k |
+--------------------+
| 0/acc_10/file_63 |
| 0/acc_499/file_64 |
| 1/acc_500/file_64 |
| 2/acc_1201/file_64 |
+--------------------+
4 rows in set (0.00 sec)mysql>
-> set user_file_path=concat(
-> substr(user_file_path,5,instr(user_file_path,'/')-5) div 500,
-> '/',user_file_path);
Query OK, 4 rows affected (0.08 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql> select * from user;
+---------+--------------------+
| user_id | user_file_path |
+---------+--------------------+
| 1 | 0/acc_10/file_63 |
| 2 | 0/acc_499/file_64 |
| 3 | 1/acc_500/file_64 |
| 4 | 2/acc_1201/file_64 |
+---------+--------------------+
4 rows in set (0.00 sec)mysql>
update user
set user_file_path=
select (cast (SUBSTR(user_file_path,5,POSITION('/' in user_file_path)-5) as integer) /500)||'/'||user_file_path;