如果列值为NULL,如何实现默认值? [英] How to achieve default value if column value is NULL?
本文介绍了如果列值为NULL,如何实现默认值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在这些条件下从表中检索一些列值.
I want to retrieve some column values from table with these conditions.
- 如果值是NULL(或)Empty String,则返回一些用户定义的值
- 如果不超出条件,则返回其值.
我该如何解决?
这是我的表格查询.
CREATE TABLE AUCTION_CAR_BID(
bid_seq bigint NOT NULL AUTO_INCREMENT,
auction_car_seq bigint NOT NULL,
bid_group_seq bigint NOT NULL,
bid_price int DEFAULT 0 NOT NULL,
over_bid_price int DEFAULT -1 NOT NULL,
result_id int DEFAULT 0 NOT NULL,
remark varchar(500),
PRIMARY KEY (bid_seq))
ENGINE = InnoDB DEFAULT CHARACTER SET utf8;
这是我努力得到的代码.
Here is my efforted codes to get it..
SELECT
COALESCE(OVER_BID_PRICE, -1)
FROM
AUCTION_CAR_BID
WHERE
BID_SEQ = 2354435345;
另一个:
SELECT
CASE
WHEN OVER_BID_PRICE IS NULL
OR TRIM(OVER_BID_PRICE) = '' THEN -1
ELSE OVER_BID_PRICE
END OVER_BID_PRICE
FROM
AUCTION_CAR_BID
WHERE
BID_SEQ = 2354435345;
但是如果给定的 id 不在我的表中,我总是得到空的字符串值(不是-1).
But I always get empty String value(not -1) if given id is not in my table.
任何建议将不胜感激!
推荐答案
如果您编写此代码:
SELECT
COALESCE(OVER_BID_PRICE, -1)
FROM
AUCTION_CAR_BID
WHERE
BID_SEQ = 2354435345;
结果可以是两种类型.
第一个结果:您的查询否"返回行!您的WHERE条件不理想,因此您将读取NULL
First result: Your query no returns rows! Your WHERE condition is unsatisfact so you'll read NULL
第二个结果:您的查询返回行,但字段的值为NULL,在这种情况下,您的COALESCE可以正常工作
Second result: Your query returns rows but the value of your field is NULL, your COALESCE works fine in this case
要解决此问题,您可以尝试以下操作:
To resolve you can try this:
SELECT COALESCE(
(SELECT
COALESCE(OVER_BID_PRICE, -1)
FROM AUCTION_CAR_BID
WHERE BID_SEQ = 2354435345)
,-1);
可以的话告诉我
这篇关于如果列值为NULL,如何实现默认值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文