如果在mysql查询中有null值，我如何设置默认值为0 ?

I want to generate a query which generate ratio girls over boys.

我想生成一个查询，生成女孩比男孩的比率。

For this i am using following query.

为此，我使用了以下查询。

SELECT (c_count_girls/c_count_boys) AS ratio FROM place WHERE placeId=1;

从placeId=1的位置选择(c_count_girls/c_count_boys)

It works fine. But whenever the values of c_count_girls=0 and c_count_boys=0

它将正常工作。但是每当c_count_girls=0, c_count_boys=0时

then this gives result like this :- ratio=null

然后得到这样的结果:- ratio=null

but i want in this case the result should like this:- ratio=0

但是我想在这种情况下，结果应该是:- ratio=0

is any such type of solution there so i can set default ratio=0 if null is the result.

如果结果为空，我可以将默认比率设为0。

Please help me. Thank you in advance.

请帮助我。提前谢谢你。

2 个解决方案

#1

SELECT COALESCE((c_count_girls/c_count_boys), 0) AS ratio
FROM place WHERE placeId=1;

More Info about COALESCE

多信息融合

#2

Try this:

试试这个:

table-name-> gender ,,,,, table-columns-> gender_id , gender_type

表名—>性别、、、、表列—> gender_id、gender_type

SQL query:

SQL查询:

select COALESCE(MAX(gender_id) , 0)+1 as max_gender_id_plus_one_fixed from gender

Output table:

输出表:

max_user_id_plus_one_fixed

max_user_id_plus_one_fixed

#1

SELECT COALESCE((c_count_girls/c_count_boys), 0) AS ratio
FROM place WHERE placeId=1;