mysql> select * from tmp;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 10 | 1 |
| 10 | 10 | 2 |
| 10 | 100 | 2 |
+------+------+------+
3 rows in set (0.00 sec)
oracle decode功能实现:
mysql> select (case when c = '2' and a = '10' then b else a end) from tmp;
+----------------------------------------------------+
| (case when c = '2' and a = '10' then b else a end) |
+----------------------------------------------------+
| 1 |
| 10 |
| 100 |
+----------------------------------------------------+
3 rows in set (0.00 sec)
分条件运算:
mysql> SELECT
-> min(CASE WHEN c = '1' THEN A ELSE B END)AS min,
-> max(CASE WHEN c = '1' THEN A ELSE B END)AS max,
-> avg(CASE WHEN c = '1' THEN A ELSE B END)AS avg
-> FROM
-> tmp;
+------+------+---------+
| min | max | avg |
+------+------+---------+
| 1 | 100 | 37.0000 |
+------+------+---------+
1 row in set (0.00 sec)
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 10 | 1 |
| 10 | 10 | 2 |
| 10 | 100 | 2 |
+------+------+------+
3 rows in set (0.00 sec)
oracle decode功能实现:
mysql> select (case when c = '2' and a = '10' then b else a end) from tmp;
+----------------------------------------------------+
| (case when c = '2' and a = '10' then b else a end) |
+----------------------------------------------------+
| 1 |
| 10 |
| 100 |
+----------------------------------------------------+
3 rows in set (0.00 sec)
分条件运算:
mysql> SELECT
-> min(CASE WHEN c = '1' THEN A ELSE B END)AS min,
-> max(CASE WHEN c = '1' THEN A ELSE B END)AS max,
-> avg(CASE WHEN c = '1' THEN A ELSE B END)AS avg
-> FROM
-> tmp;
+------+------+---------+
| min | max | avg |
+------+------+---------+
| 1 | 100 | 37.0000 |
+------+------+---------+
1 row in set (0.00 sec)