SQL排名函数-区别与应用

id  name

13 1001
12 1002
11 1002

一、Distinct（去重复记录）：  结果有相同的行时，使用Distinct 只显示一次。

二、SQL2005四个排名函数（row_number，rank，dense_rank，ntile）

1.row_number

为查询出来的每一行记录生成一个序号

select row_number() over(order by id) as row_number,* from t_table

结果:

1 11 1002
2 12 1002
3 13 1001

实际上，row_number函数生成序号的基本原理是先使用over子句中的排序语句对记录进行排序，然后按着这个顺序生成序号

也就是说使用这个语句会进行排序。如果再排序一次：

select row_number() over(order by id) as row_number,* from t_table order by name

结果：

3 13 1001
1 11 1002
2 12 1002

这时可以看到row_number的值，其实这个排序结果等价于order by name，order by id

row_number的另外一个应用，实现查询表中指定范围的记录，用于分页更简单。

with t_rowtable
as
(
　　select row_number() over(order by id) as row_number,*from t_table
)
select * from t_rowtable where row_number>1 and row_number<4 order by id

2.ran 使用方法与row_number完全相同

select rank() over(order by name desc) as row_number,name from t_table

结果：（结果中第二条记录还是为1，第三条记录为3）

1 1002
1 1002
3 1001

3.dense_rank

select dense_rank() over(order by name desc) as row_number,name from t_table

结果：（结果中第二条记录还是为1，第三条记录为2）

1 1002
1 1002
2 1001

4.ntile...

三、partition by

select row_number() over(partition by name order by id) as row_number,* from t_table

结果：

1 13 1001
1 11 1002
2 12 1002

结果中row_number：name如果没有重复则为1，如果重复则排名。

使用partition by可以用于去掉重复的某列。

select * from  (select row_number() over(partition by name order by id) as row_number,* from t_table) b where row_number=1

结果：

1 13 1001
1 11 1002
这样name就不会有重复的了。

 四、charindex

charindex( expression1 , expression2 [ , start_location ] )

解释：在 expression2 中搜索 expression1 中的起始位置。

从expression2 的第start_location字符位置开始搜索。如果没有给定 start_location，则将从 expression2 的起始位置开始搜索。

返回类型 int

实例：

select * from table_a where charindex('a',id)>0 or charindex('b',id)>0
table_a 表中 id字段中含有"a"或者"b"的记录

select charindex (’wonderful’, notes, 5) from titles   从notes 列的第五个字符开始寻找"wonderful"。