具有 NULL 和 0 行的 MySql 乘法列

Question

我有这个 MySQL 表:

+----+-------------+----------+------+-------+
| ID | DESCRIPTION | QUANTITY | EACH | PRICE |
+----+-------------+----------+------+-------+
| 1  | Product 1   |     1    |  12  | 1*12  |
| 2  | Product 2   |     2    |   3  | 2* 3  |
| 3  | Product 3   |   NULL   |   3  |       |
| 4  | Product 4   |     0    |   7  |       |
+----+-------------+----------+------+-------+

这个查询:

SELECT
  DESCRIPTION,
  QUANTITY,
  EACH,
  COALESCE(QUANTITY, 1) * EACH AS PRICE
FROM table1

我想用 1 替换 NULL 和 0，并且我想在 PRICE 列中进行乘法运算。我不想使用 UPDATE，因为我无法更改 table1 中的值。谢谢!

Answer 1

尝试下面的查询

SELECT
  DESCRIPTION,
  QUANTITY,
  EACH,
  COALESCE(IF(QUANTITY = NULL OR QUANTITY = 0,1,QUANTITY), 1) * EACH AS PRICE
FROM table1