Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
754 views
in Technique[技术] by (71.8m points)

mysql - How set 0 with MAX function when it is NULL?

I would like to understand how to set 0 value of the attribute when it is NULL with MAX function. For example:

Name columns:
number - date

Values:
10 - 2012-04-04
11 - 2012-04-04
12 - 2012-04-04
13 - 2012-04-15
14 - 2012-06-21
 1 - 2013-07-04

Number is incremental field, but it has set itself 1 when new year has come. But result of:

SELECT (MAX(number)+1) number WHERE date LIKE "2014%" 

is NULL and not 1 because MAX(number) is NULL and not 0

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Well, as there is no date like 2014, you would expect null, because the maximum of nothing is actually not anyting.

But do this:

COALESCE(MAX(number),0)

Which means: get the first non-null thing from the next list, so if your max is null, it'll give you 0


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...