在MySQL中将列与NULL行相乘？
要与NULL行相乘，可以使用COALESCE()。让我们首先创建一个表-
mysql> create table DemoTable1842
(
NumberOfItems int,
Amount int
);使用插入命令在表中插入一些记录-
mysql> insert into DemoTable1842 values(10,40); mysql> insert into DemoTable1842 values(20,5); mysql> insert into DemoTable1842 values(NULL,10);
使用select语句显示表中的所有记录-
mysql> select * from DemoTable1842;
这将产生以下输出-
+---------------+--------+ | NumberOfItems | Amount | +---------------+--------+ | 10 | 40 | | 20 | 5 | | NULL | 10 | +---------------+--------+ 3 rows in set (0.00 sec)
这是将列与NULL行相乘的查询-
mysql> select NumberOfItems,Amount,
coalesce(NumberOfItems,1)*Amount as Total
from DemoTable1842;这将产生以下输出-
+---------------+--------+-------+ | NumberOfItems | Amount | Total | +---------------+--------+-------+ | 10 | 40 | 400 | | 20 | 5 | 100 | | NULL | 10 | 10 | +---------------+--------+-------+ 3 rows in set (0.00 sec)