本人的想法是基于if(1=1,10,20)来做的,具体如下:
首先表名是EMPLOYEE_TBL。其中的数据CITY中有:
+--------------+| CITY |
+--------------+
| WHITELAND |
| INDIANAPOLIS |
| GREENWOOD |
| INDIANAPOLIS |
| INDIANAPOLIS |
+--------------+
如果在ORACLE中运行:
mysql> SELECT CITY,DECODE(CITY,'INDIANAPOLIS','INDY','GREENWOOD','GREEN','OTHER')DECODE_CITY
FROM EMPLOYEE_TBL;运行结果如下:
+--------------+-------------+
| CITY | DECODE_CITY |
+--------------+-------------+
| WHITELAND | OTHER |
| INDIANAPOLIS | INDY |
| GREENWOOD | GREE |
| INDIANAPOLIS | INDY |
| INDIANAPOLIS | INDY |
+--------------+-------------+
但是mysql中decode()函数用来加密或者比较,不支持这样写,
所以可以这样写来实现decode()函数:
mysql> SELECT CITY,
IF(CITY='INDIANAPOLIS','INDY',IF(CITY='GREENWOOD','GREE','OTHER'))DECODE_CITY
FROM EMPLOYEE_TBL;
可得同样结果。如果在decode()中只要一对修改参数,在mysql中实现也如上:
mysql> SELECT CITY,if(CITY='GREENWOOD','GREE','OTHER') FROM EMPLOYEE_TBL;