在我们写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 apartition of the set. The key difference is that if rows with tied values existwithin the partition, they will receive the same rank value, and the nextvalue will receive the rank value as if there had been no ties, producing a gapbetween assigned numbers. |
||||||||||
DENSE_RANK |
The difference between DENSE_RANK and RANK is that DENSE_RANK doesn’thave gaps in the rank values when there are tied values; the next value hasthe next rank assignment. |
||||||||||
NTILE |
NTILE divides the result set into a specified number of groups, based on the
|