T-SQL Recipes之Common Function

在我们写SQL的时候,经常会用到许多内置方法,简化了我们许多代码,也提高了效率,这篇主要总结一些常用的方法。

ISNULL VS COALESCE VS NULLIF

 在SQL中,NULL值是比较特殊的,所以如何处理NULL值也是要注意的:

  • NULL + 10 = NULL
  • NULL OR TRUE = NULL
  • NULL OR FALSE = NULL

 首先我们看一下这三个方法的定义吧:

 

Function  Description
 ISNULL
 ISNULL validates whether an expression is NULL and, if so, replaces the NULL value with an alternate value.
 COALESCE
 The COALESCE function returns the first non-NULL value from a provided list of expressions.
 NULLIF
 NULLIF returns a NULL value when the two provided expressions have the same value. Otherwise, the first expression is returned.

 那如何在ISNULL和COALESCE中选择呢:

  • ISNULL容易拼写,让人感觉更简洁,直观。但在多链上面的写法很容易让人晕迷,比如:ISNULL(value1, ISNULL(value2, ISNULL(value3, '')))。这个时候推荐使用COALESCE了
  • COALESCE很灵活,而且是ANSI标准SQL的一部分。所以可移值性很好,如果在跨平台上的时候写SQL的时候,推荐COALESCE

 Windowing Functions

Function  Description
 ROW_NUMBER
ROW_NUMBER returns an incrementing integer for each row within a partition of a set.
ROW_NUMBER will return a unique number within each partition,starting with 1.
 RANK
 Similar to ROW_NUMBER, RANK increments its value for each row within a
partition of the set. The key difference is that if rows with tied values exist
within the partition, they will receive the same rank value, and the next
value will receive the rank value as if there had been no ties, producing a gap
between assigned numbers.
 DENSE_RANK
The difference between DENSE_RANK and RANK is that DENSE_RANK doesn’t
have gaps in the rank values when there are tied values; the next value has
the next rank assignment.
NTILE
NTILE divides the result set into a specified number of groups, based on the
ordering and optional partition clause.

 

posted @ 2016-06-01 15:36  Jeffrey Chan  阅读(136)  评论(0编辑  收藏  举报