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"。