Mysql if field null取一些值

时间:2021-12-23 02:59:12

I have such code: (on RoR):

我有这样的代码:(在RoR上):

@types = Type.where("TYP_MOD_ID = ? AND (SUBSTRING(TYP_PCON_START,1,4) <= ?) AND (SUBSTRING(TYP_PCON_END,1,4) >= ?) AND TYP_KV_FUEL_DES_ID = ? ", params[:models], params[:year], params[:year], params[:fueltype]).order("TYP_HP_FROM")

But sometimes TYP_PCON_END is null in db.... How in such case use not null, but my value, sended by ruby (date.now(YEAR))?

但有时TYP_PCON_END在db中是空的....在这种情况下如何使用not null,但是我的值,由ruby(date.now(YEAR))发出?

So how if field is null use another value? How to check on null, and use my code if not null (SUBSTRING(TYP_PCON_END,1,4) >= ?) and another:

那么如果field为null又如何使用另一个值呢?如何检查null,如果不为null则使用我的代码(SUBSTRING(TYP_PCON_END,1,4)> =?)和另一个:

? >= ?

if null?

1 个解决方案

#1


0  

You can use COALESCE:

你可以使用COALESCE:

SUBSTRING(COALESCE(TYP_PCON_END, 'some default string'),1,4) > ?

If you don't need the substring for your default value, then you would just do it this way:

如果您不需要子字符串作为默认值,那么您只需这样做:

COALESCE(SUBSTRING(TYP_PCON_END,1,4), 'AAAA') > ?

This works since doing a SUBSTRING on a null value will yield null as well.

这是有效的,因为对空值执行SUBSTRING也将产生null。

#1


0  

You can use COALESCE:

你可以使用COALESCE:

SUBSTRING(COALESCE(TYP_PCON_END, 'some default string'),1,4) > ?

If you don't need the substring for your default value, then you would just do it this way:

如果您不需要子字符串作为默认值,那么您只需这样做:

COALESCE(SUBSTRING(TYP_PCON_END,1,4), 'AAAA') > ?

This works since doing a SUBSTRING on a null value will yield null as well.

这是有效的,因为对空值执行SUBSTRING也将产生null。