本文介绍了如果列值为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,如何实现默认值?
发布评论