mysql函数coalesce

一时失言乱红尘 2022-10-06 05:46 156阅读 0赞

很多人知道ISNULL函数，但是很少人知道Coalesce函数，人们会无意中使用到Coalesce函数，并且发现它比ISNULL更加强大，其实到目前为止，这个函数的确非常有用，本文主要讲解其中的一些基本使用：

#  首先看看联机丛书的简要定义： #

返回其参数中第一个非空表达式

# 语法： #

COALESCE ( expression [ ,...n ] )

如果所有参数均为 NULL，则 COALESCE 返回 NULL。至少应有一个 Null 值为 NULL 类型。尽管 ISNULL 等同于 COALESCE，但它们的行为是不同的。包含具有非空参数的 ISNULL 的表达式将视为 NOT NULL，而包含具有非空参数的 COALESCE 的表达式将视为 NULL。在 SQL Server 中，若要对包含具有非空参数的 COALESCE 的表达式创建索引，可以使用 PERSISTED 列属性将计算列持久化，如以下语句所示：

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  **CREATE** **TABLE** \#CheckSumTest   
2.       (  
3.           ID **int** identity ,  
4.           Num **int** **DEFAULT** ( RAND() \* 100 ) ,  
5.           RowCheckSum **AS** COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED **PRIMARY** **KEY**  
6.       );

# 下面来看几个比较有用的例子： #

## 首先，从MSDN上看看这个函数的使用方法，coalesce函数（下面简称函数），返回一个参数中非空的值。如： ##

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  **SELECT**  COALESCE(NULL, NULL, GETDATE())

由于两个参数都为null，所以返回getdate（）函数的值，也就是当前时间。即返回第一个非空的值。由于这个函数是返回第一个非空的值，所以参数里面必须最少有一个非空的值，如果使用下面的查询，将会报错：

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  **SELECT**  COALESCE(NULL, NULL, NULL)

![1355599572_1474.png][]

## 然后来看看把函数应用到Pivot中，下面语句在AdventureWorks 数据库上运行： ##

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  **SELECT**  **Name**  
2.   **FROM**    HumanResources.Department  
3.   **WHERE**   ( GroupName= 'Executive Generaland Administration' )

会得到下面的结果：

![1355599633_8518.png][]

## 如果想扭转结果，可以使用下面的语句： ##

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  **DECLARE** @DepartmentName **VARCHAR**(1000)  
2.      
3.   **SELECT**  @DepartmentName = COALESCE(@DepartmentName, '') + **Name** + ';'  
4.   **FROM**    HumanResources.Department  
5.   **WHERE**   ( GroupName= 'Executive Generaland Administration' )  
6.      
7.   **SELECT**  @DepartmentName **AS** DepartmentNames  
8.

![1355599723_3721.png][]

## 使用函数来执行多条SQL命令： ##

当你知道这个函数可以进行扭转之后，你也应该知道它可以运行多条SQL命令。并且使用分号来区分独立的操作。下面语句是在Person架构下，有名字为Name的列的值：

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  **DECLARE** @SQL **VARCHAR**(**MAX**)   
2.     
3.   **CREATE** **TABLE** \#TMP   
4.      (Clmn **VARCHAR**(500),    
5.       Val **VARCHAR**(50))   
6.     
7.   **SELECT** @SQL=COALESCE(@SQL,'')+CAST('INSERT INTO \#TMP Select ''' + TABLE\_SCHEMA + '.' + TABLE\_NAME + '.'   
8.   + COLUMN\_NAME + ''' AS Clmn, Name FROM ' + TABLE\_SCHEMA + '.\[' + TABLE\_NAME +   
9.   '\];' **AS** **VARCHAR**(**MAX**))   
10.  **FROM** INFORMATION\_SCHEMA.COLUMNS    
11.  JOIN sysobjects B **ON** INFORMATION\_SCHEMA.COLUMNS.TABLE\_NAME = B.**NAME**   
12.  **WHERE** COLUMN\_NAME = 'Name'    
13.     AND xtype = 'U'    
14.     AND TABLE\_SCHEMA = 'Person'   
15.    
16.  PRINT @SQL   
17.  **EXEC**(@SQL)   
18.    
19.  **SELECT** \* **FROM** \#TMP   
20.  **DROP** **TABLE** \#TMP

![1355599793_4176.png][]

` `

``

## 还有一个很重要的功能：。当你尝试还原一个库，并发现不能独占访问时，这个功能非常有效。我们来打开多个窗口，来模拟一下多个连接。然后执行下面的脚本： ##

**\[sql\]**  [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  **DECLARE** @SQL **VARCHAR**(8000)  
2.      
3.   **SELECT**  @SQL = COALESCE(@SQL, '') + 'Kill ' + CAST(spid **AS** **VARCHAR**(10)) + '; '  
4.   **FROM**    sys.sysprocesses  
5.   **WHERE**   DBID = DB\_ID('AdventureWorks')  
6.      
7.   PRINT @SQL \--EXEC(@SQL) Replace the print statement with exec to execute

结果如下：

![1355599913_7831.png][]

然后你可以把结果复制出来，然后一次性杀掉所有session。

[view plain]: http://blog.csdn.net/dba_huangzj/article/details/8300784#
[1355599572_1474.png]: https://img-my.csdn.net/uploads/201212/16/1355599572_1474.png
[1355599633_8518.png]: https://img-my.csdn.net/uploads/201212/16/1355599633_8518.png
[1355599723_3721.png]: https://img-my.csdn.net/uploads/201212/16/1355599723_3721.png
[1355599793_4176.png]: https://img-my.csdn.net/uploads/201212/16/1355599793_4176.png
[1355599913_7831.png]: https://img-my.csdn.net/uploads/201212/16/1355599913_7831.png

发表评论取消回复

表情：

评论列表（有 0 条评论，156人围观）

还没有评论，来说两句吧...

相关阅读

相关 PostgreSQL COALESCE函数

COALESCE函数是返回参数中的第一个非null的值，它要求参数中至少有一个是非null的，如果参数都是null会报错。 select COALESCE(null,

朴灿烈づ我的快乐病毒、/ 2022年12月09日 04:53/ 0 赞/ 127 阅读

相关 sql：函数：COALESCE()

COALESCE()函数主流数据库系统都支持COALESCE()函数，这个函数主要用来进行空值处理，其参数格式如下： COALESCE ( expression,v

我不是女神ヾ/ 2022年11月20日 05:50/ 0 赞/ 135 阅读

相关 mysql函数coalesce

很多人知道ISNULL函数，但是很少人知道Coalesce函数，人们会无意中使用到Coalesce函数，并且发现它比ISNULL更加强大，其实到目前为止，这个函数的确非常有用，

一时失言乱红尘/ 2022年10月06日 05:46/ 0 赞/ 157 阅读

相关 COALESCE()函数

sql 中的COALESCE()函数对于Oracle数据库，一般经常对空值处理的函数为NVL，而mysql中常用到的是ifnull，这两个函数相似，其实都是由一个函数衍生

阳光穿透心脏的1/2处/ 2022年09月13日 14:25/ 0 赞/ 134 阅读

相关 Mysql coalesce()函数认识和用法

Mysql coalesce()函数认识和用法 coalesce()解释：返回参数中的第一个非空表达式（从左向右）；鉴于在 [mysql][]中没

我会带着你远行/ 2022年08月20日 06:14/ 0 赞/ 134 阅读

相关 mysql中coalesce函数

SELECT COALESCE(a, b, c); 参数说明：如果anull,则选择b；如果bnull,则选择c；如果a!=null,则选择a；如果a b c 都为null

旧城等待，/ 2022年04月12日 12:28/ 0 赞/ 135 阅读

相关 SQL: coalesce()函数

coalesce()函数 1.用途：将空值替换成其他值返回第一个非空值 2.表达式： COALESCE是一个函数， (expression\_1, expre

小咪咪/ 2021年11月23日 03:10/ 0 赞/ 310 阅读

相关 mysql 中NULLIF() 、ISNULL()、COALESCE()函数的用法

在进行查询语句的时候会遇到各种条件判断的时候，在本篇文章中将讲解NULLIF() 、ISNULL()、COALESCE()函数的用法。 1. NULLIF(exp1,exp2

忘是亡心i/ 2021年09月14日 14:30/ 0 赞/ 354 阅读

相关 MySql coalesce函数

语法 coalesce(str1,str2,str3,…,default) str1,str2,str3：参数，但是最终只会返回最左边那个非空参数的值。 d

àì夳堔傛蜴生んèń/ 2021年06月22日 15:36/ 0 赞/ 479 阅读

相关警告！MySQL函数IFNULL和COALESCE的注意事项

假设现在有一张表： CREATE TABLE `demo` ( `id` int(11) NOT NULL, `score` int(11)

小灰灰/ 2021年05月10日 11:33/ 0 赞/ 399 阅读