mysql替换查询和模糊查询

nychenlei

mysql> select * from alert_config limit 1;   
                                                                                      
+----+---------------+-------------+-------------+------------+------+--------+---------------------+
| id | ip            | description | explanation | upperLimit | type | status | createTime          |
+----+---------------+-------------+-------------+------------+------+--------+---------------------+
|  1 | 192.168.0.126 |
[email=cpu@value@%]cpu@value@%[/email]
| NULL        |         90 |    1 |      0 | 2009-06-24 12:00:53 |
+----+---------------+-------------+-------------+------------+------+--------+---------------------+
1 row in set (0.00 sec)

mysql> select * from alert_mobile_history_log limit 1;
                                                                             
+----+---------+-------+---------------------+
| id | alertId | value | createTime          |
+----+---------+-------+---------------------+
|  1 |       1 | 0     | 2009-06-24 11:55:03 |
+----+---------+-------+---------------------+
1 row in set (0.00 sec)

mysql> select a.ip,b.alertId,a.description,b.value,b.createTime from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId limit 1;
                                                                                         
+---------------+---------+-------------+-------+---------------------+
| ip            | alertId | description | value | createTime          |
+---------------+---------+-------------+-------+---------------------+
| 192.168.0.126 |       1 |
[email=cpu@value@%]cpu@value@%[/email]
| 0     | 2009-06-24 11:55:03 |
+---------------+---------+-------------+-------+---------------------+
1 row in set (0.00 sec)

要把description 字段下的@value@替换为value字段下的值。

mysql> select a.ip,b.alertId,replace(a.description,'@value@',b.value) as value2,b.createTime from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId limit 1;
                                                         
+---------------+---------+--------+---------------------+
| ip            | alertId | value2 | createTime          |
+---------------+---------+--------+---------------------+
| 192.168.0.126 |       1 | cpu0%  | 2009-06-24 11:55:03 |
+---------------+---------+--------+---------------------+
1 row in set (0.00 sec)

查询value2字段下有load的数据。

mysql> select a.ip,b.alertId,replace(a.description,'@value@',b.value) as value2,b.createTime from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId and value2 like 'load%' limit 1;   
ERROR 1054 (42S22): Unknown column 'value2' in 'where clause'

value2不能做为条件使用。

mysql> select a.ip,b.alertId,replace(a.description,'@value@',b.value) as value2,b.createTime from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId and a.description like 'load%' limit 1;
+---------------+---------+-----------+---------------------+
| ip            | alertId | value2    | createTime          |
+---------------+---------+-----------+---------------------+
| 192.168.0.126 |       3 | load 0.00 | 2009-06-24 15:40:02 |
+---------------+---------+-----------+---------------------+
1 row in set (0.00 sec)

ok！


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/101226/showart_2018470.html