mysql如何将另一个值而不是 NULL 示一笔?

标签: MySQL
发布时间: 2015/6/11 22:25:45
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

如何可以显示而不是 NULL 的"1"?

在这里我的表:

|policies|
    |id| |policy|
      1    abc
      2    def

|insurances|
     |id|  |policy_id| |money|
       1         1       1000
       2         1       2000  
       3         2       3000
       4         2       4000 
       5         1       1000

下面的查询:

 select sum(money) as cash from insurances i
 INNER JOIN policies p ON p.id = i.policy_id
 WHERE i.policy_id = 3
 Group by i.policy_id

在此情况下,当 policy_id = 3 它显示 NULL:

 |cash|
   NULL 

我想要显示:

 |cash|
   1

我也试过了,但没有工作:

 select sum(money)+1 as cash from insurances i
 INNER JOIN policies p ON p.id = i.policy_id
 WHERE i.policy_id = 3
 Group by i.policy_id;

这里是链接

解决方法 1:

SELECT COALESCE(sum(money), 1) as Cash
FROM (
  SELECT policy_id, money
  FROM
    insurances i INNER JOIN policies p
    ON p.id = i.policy_id
  WHERE
    i.policy_id = 1
) s

编辑,或者你可以尝试用这个小窍门:

SELECT COALESCE(MAX(cash), 1) FROM (
  SELECT sum(money) AS cash
  FROM   insurances i
         INNER JOIN policies p ON p.id = i.policy_id
  WHERE i.policy_id = 3
  GROUP BY i.policy_id
) s
赞助商