如何在MySQL中使用Coalesce

A little help here. I really don't understand how to use this coalesce in MySQL

这里有点帮助。我真的不明白如何在MySQL中使用这个coalesce

I have read all the pages in page 1 result of how to use coalsece in google result.

我已阅读第1页中的所有页面如何在google结果中使用coalsece的结果。

I know its meaning that it returns the first non-null value it encounters and null otherwise.

我知道它的意思是它返回它遇到的第一个非null值，否则返回null。

But it's still vague for me.

但它对我来说仍然含糊不清。

How come I saw queries that returns multiple values? Isn't it only the first not null value that is returned?
为什么我看到返回多个值的查询？是不是只返回了第一个not null值？
And how do it decide which column to base? coalesce(column1,column2)? what if first column is null and other column is not null?
它如何决定基于哪一列？合并（列1，列2）？如果第一列为空而其他列不为空怎么办？
Or if I'm wrong or my syntax is wrong, how do i properly write it?
或者，如果我错了或我的语法错误，我该怎么写呢？
Can someone provide a very good and simple example on how to use it?
有人可以提供一个如何使用它的非常好的简单示例吗？
And when it is desirable to use.
并且当需要使用时。

4 个解决方案

#1

How come i saw queries that returns multiple values? isnt it only the first not null value that is returned?

为什么我看到返回多个值的查询？是不是只返回了第一个not null值？

Yes, it is only the first non-NULL value that is returned. You must be mistaken about the queries you have seen where you thought that was not the case: if you could show us an example, we might be able to help clarify the misunderstanding.

是的，它只是返回的第一个非NULL值。您必须误解您所看到的查询，而您认为并非如此：如果您能向我们展示一个示例，我们可能会帮助澄清误解。
And how do it decide which column to base? coalesce(column1,column2)? what if first column is null and other column is not null?

它如何决定基于哪一列？合并（列1，列2）？如果第一列为空而其他列不为空怎么办？

In order of its arguments: in this example, column1 before column2.

按其参数顺序：在此示例中，column1之前的column1。
Or if im wrong or my syntax is wrong, how do i properly write it?

或者如果我错了或我的语法错了，我该怎么写呢？

You're not wrong.

你没错。
Can someone provide a very good and simple example on how to use it?

有人可以提供一个如何使用它的非常好的简单示例吗？

Taken from the documentation:

取自文档：
```
mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL
```
And when it is desirable to use.

并且当需要使用时。

It is desirable to use whenever one wishes to select the first non-NULL value from a list.

无论何时希望从列表中选择第一个非NULL值，都希望使用。

#2

I personally use coalesce when I want to find the first column that isn't blank in a row from a priority list.

当我想从优先级列表中找到第一列不是空行的时候，我个人使用coalesce。

Say for example I want to get a phone number from a customer table and they have 3 columns for phone numbers named mobile, home and work, but I only want to retrieve the first number that isn't blank.

比方说，我想从客户表中获取一个电话号码，他们有3列名为mobile，home和work的电话号码，但我只想检索第一个非空白的号码。

In this instance, I have the priority of mobile, then home and then work.

在这种情况下，我有移动优先权，然后回家然后工作。

TABLE STRUCTURE
--------------------------------------------
| id | customername | mobile | home | work |
--------------------------------------------
| 1  | Joe          | 123    | 456  | 789  |
--------------------------------------------
| 2  | Jane         |        | 654  | 987  |
--------------------------------------------
| 3  | John         |        |      | 321  |
--------------------------------------------

SELECT id, customername, COALESCE(mobile, home, work) AS phone FROM customers

RESULT
------------------------------
| id | customername | phone  |
------------------------------
| 1  | Joe          | 123    |
------------------------------
| 2  | Jane         | 654    |
------------------------------
| 3  | John         | 321    |
------------------------------

#3

COALESCE will return the first non-null column or value.

COALESCE将返回第一个非空列或值。

Example Usage:

用法示例：

SELECT COALESCE(my_column, my_other_column, 'default') as username FROM my_table;

Example results:

示例结果：

my_column  my_other_column     results
null       null                'default'
null       0                   '0'
null       'jimmy'             'jimmy'
'bob'      'jimmy'             'bob'

#4

This is a perfect example of the Coalesce if will change the null fields/columns to what you want them to be in the case from null to 0 and 1. Copy this into a mysql editor it will look a lot better

这是Coalesce的一个完美示例，如果将空字段/列更改为您希望它们在从null到0和1的情况下将其复制到mysql编辑器中它看起来会好很多

Select "Column1 (Dont Want Touched)", coalesce(column2(That you want set to 0 if null),0) as column2 (give it same name as was e.g. "column2"), coalesce(column3(Instead of null set to 1),1) as column3(give it same name as was e.g. "column3")
from "MydataTable" Where 'somedates' in ('2015-04-10','2015-04-03','2015-03-27','2015-04-17') and id = 10 order by 'somedates ';

选择“Column1（Dont Want Touched）”，合并（column2（如果为null则设置为0），0）为column2（给它的名称与例如“column2”相同），coalesce（column3（而不是null设置为1），1）作为第3栏（与其名称相同，例如“column3”）来自“MydataTable”其中“somedates”（'2015-04-10'，'2015-04-03'，'2015-03- 27'，'2015-04-17'）和id = 10命令'somedates';

#1

How come i saw queries that returns multiple values? isnt it only the first not null value that is returned?

为什么我看到返回多个值的查询？是不是只返回了第一个not null值？

Yes, it is only the first non-NULL value that is returned. You must be mistaken about the queries you have seen where you thought that was not the case: if you could show us an example, we might be able to help clarify the misunderstanding.

是的，它只是返回的第一个非NULL值。您必须误解您所看到的查询，而您认为并非如此：如果您能向我们展示一个示例，我们可能会帮助澄清误解。
And how do it decide which column to base? coalesce(column1,column2)? what if first column is null and other column is not null?

它如何决定基于哪一列？合并（列1，列2）？如果第一列为空而其他列不为空怎么办？

In order of its arguments: in this example, column1 before column2.

按其参数顺序：在此示例中，column1之前的column1。
Or if im wrong or my syntax is wrong, how do i properly write it?

或者如果我错了或我的语法错了，我该怎么写呢？

You're not wrong.

你没错。
Can someone provide a very good and simple example on how to use it?

有人可以提供一个如何使用它的非常好的简单示例吗？

Taken from the documentation:

取自文档：
```
mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL
```
And when it is desirable to use.

并且当需要使用时。

It is desirable to use whenever one wishes to select the first non-NULL value from a list.

无论何时希望从列表中选择第一个非NULL值，都希望使用。

#2