就像这样:
SET v_tax_rate =
CASE state
WHEN 'AL', 'AK' THEN 0.04
WHEN 'CA' THEN 0.06
WHEN 'WY' THEN 0.03
END;
我把"AL"和"AK"放在一个WHEN中。但它不起作用。我无法成功创建它,收到错误提示。
是否可以在MySQL中这样做?
根据当前的 12.4 控制流函数 :: CASE 语法,理解和正确处理警告,一种选择是执行以下操作:
mysql> DROP TABLE IF EXISTS `tbl_sta`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `tbl_sta` (
-> `state` CHAR(2) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `tbl_sta`
-> (`state`)
-> VALUES
-> ('AL'), ('AK'),
-> ('CA'), ('WY');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT
-> `state`,
-> CASE `state`
-> WHEN `state` NOT IN ('AL', 'AK') THEN 0.04
-> WHEN 'CA' THEN 0.06
-> WHEN 'WY' THEN 0.03
-> END `result`
-> FROM `tbl_sta`;
+-------+--------+
| state | result |
+-------+--------+
| AL | 0.04 |
| AK | 0.04 |
| CA | 0.06 |
| WY | 0.03 |
+-------+--------+
4 rows in set, 4 warnings (0.01 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'AL' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'AK' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'CA' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'WY' |
+---------+------+----------------------------------------+
4 rows in set (0.00 sec)
mysql> SELECT 'AL' = 0 /* FALSE */, 'AL' = 1 /* TRUE */;
+----------+----------+
| 'AL' = 0 | 'AL' = 1 |
+----------+----------+
| 1 | 0 |
+----------+----------+
1 row in set, 2 warnings (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'AL' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'AL' |
+---------+------+----------------------------------------+
2 rows in set (0.00 sec)
另一个选项(更详细,但没有警告(:
mysql> SELECT
-> `state`,
-> CASE `state`
-> WHEN IF(`state` IN ('AL', 'AK'), `state`, NULL) THEN 0.04
-> WHEN 'CA' THEN 0.06
-> WHEN 'WY' THEN 0.03
-> END `result`
-> FROM `tbl_sta`;
+-------+--------+
| state | result |
+-------+--------+
| AL | 0.04 |
| AK | 0.04 |
| CA | 0.06 |
| WY | 0.03 |
+-------+--------+
4 rows in set (0.00 sec)
还有另一种选择:
mysql> SELECT
-> `state`,
-> CASE `state`
-> WHEN CASE WHEN `state` IN ('AL', 'AK')
-> THEN `state` END THEN 0.04
-> WHEN 'CA' THEN 0.06
-> WHEN 'WY' THEN 0.03
-> END `result`
-> FROM `tbl_sta`;
+-------+--------+
| state | result |
+-------+--------+
| AL | 0.04 |
| AK | 0.04 |
| CA | 0.06 |
| WY | 0.03 |
+-------+--------+
4 rows in set (0.00 sec)
参见数据库小提琴。