1.排名函数:
SQL2005引进了4个新的排序函数,分别是 row_number rank dense_rank ntile .我不知道怎么排版写,就散乱地写点吧。
a.row_number :
row_number()不仅在排名上有作用,而且在许多实际问题上都有不错的应用,效率也非常不错.
排名函数 就是用来为记录添上编号--行号。这里说个分区排名的用法的内在介绍吧
row_number() over(partition by col1 order by col2)
这里就是需要先按分区列再按排序列对数据排序。 你看执行计划,会发现一个叫系列摄影的运算符号,它负责计算排名值.对于表中的每条记录,她要检查2个方面.
1) 该行是否为分区的第一行? 意思就是检查你COL1列的值和上一个PARTITION BY的值是否相同。如果不同,就说明它是第一行,那么返回一个TRUE;
否则返回FALSE;返回TRUE就要重新排名,从1开始.普通的ROW_NUMBER() 没有分区 那么它只有第一行为TRUE 其他都是false,则一直排序下去。
2) 该行是否不同于上一个值?意思就是和上面一个值比较.就相当于在一个分区内根据条件的排序规则排序。这里的ROW_NUMBER 是一定会为每行递增下去的,
但是RANK DECSE_RANK是会经过判断后,决定是否递增的.
介绍几个情况:
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Sales') IS NOT NULL
DROP TABLE dbo.Sales;
GO
CREATE TABLE dbo.Sales
(
empid VARCHAR(10) NOT NULL PRIMARY KEY,
mgrid VARCHAR(10) NOT NULL,
qty INT NOT NULL
);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('A', 'Z', 300);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('B', 'X', 100);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('C', 'X', 200);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('D', 'Y', 200);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('E', 'Z', 250);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('F', 'Z', 300);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('G', 'X', 100);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('H', 'Y', 150);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('I', 'X', 250);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('J', 'Z', 100);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('K', 'Y', 200);
CREATE INDEX idx_qty_empid ON dbo.Sales(qty, empid);
CREATE INDEX idx_mgrid_qty_empid ON dbo.Sales(mgrid, qty, empid);
如果有唯一的列
只根据EMPID排序)
2000:
SELECT empid,
(SELECT COUNT(*)
FROM dbo.Sales AS S2
WHERE S2.empid <= S1.empid) AS rownum
FROM dbo.Sales AS S1
ORDER BY empid;
2005:
select empid,
rownum=ROW_NUMBER() over(order by empid)
from Sales
--顺便提下:通过执行计划你可以清楚看到后者强烈的优势
如果只有一列不够唯一,需要加附加列来确定唯一的排名(根据qty,empid排序)
--2000
SELECT empid, qty,
(SELECT COUNT(*)
FROM dbo.Sales AS S2
WHERE S2.qty < S1.qty
OR (S2.qty = S1.qty AND S2.empid <= S1.empid)) AS rownum
FROM dbo.Sales AS S1
ORDER BY qty, empid;
--2005
select
empid,qty,
rownum=ROW_NUMBER()over(order by qty,empid)
from Sales
--顺便提下:通过执行计划你可以清楚看到后者强烈的优势
如果只有一列,而且没有附加的列
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1(col1 VARCHAR(5));
INSERT INTO dbo.T1(col1) VALUES('A');
INSERT INTO dbo.T1(col1) VALUES('A');
INSERT INTO dbo.T1(col1) VALUES('A');
INSERT INTO dbo.T1(col1) VALUES('B');
INSERT INTO dbo.T1(col1) VALUES('B');
INSERT INTO dbo.T1(col1) VALUES('C');
INSERT INTO dbo.T1(col1) VALUES('C');
INSERT INTO dbo.T1(col1) VALUES('C');
INSERT INTO dbo.T1(col1) VALUES('C');
INSERT INTO dbo.T1(col1) VALUES('C');
--2000
SELECT number + smaller AS rownum, col1
FROM (SELECT col1, COUNT(*) AS dups,
(SELECT COUNT(*) FROM dbo.T1 AS B
WHERE B.col1 < A.col1) AS smaller
FROM dbo.T1 AS A
GROUP BY col1) AS D, master..spt_values
WHERE number <= dups and type='p'
order by col1
--2005
select col1,
rownum=ROW_NUMBER() over(order by col1 )
from t1
可能大家发现了一个东西我没写 IDENTITY.它其实是2005之前一个计算行号非常常用效率也很高的方法.
一般的思路就是:通过IDENTITY带着数据放入一个临时表,之后就可以用行号了,就是多了个临时表.
当然你也可以用游标处理:
DECLARE @SalesRN TABLE(empid VARCHAR(5), qty INT, rn INT);
DECLARE @empid AS VARCHAR(5), @qty AS INT, @rn AS INT;
BEGIN TRAN
DECLARE rncursor CURSOR FAST_FORWARD FOR
SELECT empid, qty FROM dbo.Sales ORDER BY qty, empid;
OPEN rncursor;
SET @rn = 0;
FETCH NEXT FROM rncursor INTO @empid, @qty;
WHILE @@fetch_status = 0
BEGIN
SET @rn = @rn + 1;
INSERT INTO @SalesRN(empid, qty, rn) VALUES(@empid, @qty, @rn);
FETCH NEXT FROM rncursor INTO @empid, @qty;
END
CLOSE rncursor;
DEALLOCATE rncursor;
COMMIT TRAN
SELECT empid, qty, rn FROM @SalesRN;
--关于游标应该尽量避免使用,因为它包含太多的开销.当然在分区非常小,它的速度也会非常快。一般表越小,它的速度也越快。
经过书上测试:这几个方法排序是->row_number()<identity<cursor<2000的子查询
b.RANK ,Dense RANK,NTILE
它们分别用于排名 积分应用 计算百分比或组中排列各个元素
它们区别我直接给出例子,就可以很清楚的看到
create table rankorder(
orderid int,
qty int
)
go
--插入数据
insert rankorder values(30,10)
insert rankorder values(10,10)
insert rankorder values(80,10)
insert rankorder values(40,10)
insert rankorder values(30,15)
insert rankorder values(30,20)
insert rankorder values(22,20)
insert rankorder values(21,20)
insert rankorder values(10,30)
insert rankorder values(30,30)
insert rankorder values(40,40)
go
SELECT orderid,qty,
ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
RANK() OVER(ORDER BY qty) AS [rank],
DENSE_RANK() OVER(ORDER BY qty) AS denserank ,
NTILE(3) OVER(ORDER BY qty) AS [NTILE]
FROM rankorder
ORDER BY qty
--结果
--ROW_NUMBER()是按qty由小到大逐一排名,不并列,排名连续
--RANK()是按qty由小到大逐一排名,并列,排名不连续
--DENSE_RANK()是按qty由小到大逐一排名,并列,排名连续
--NTILE()是按qty由小到大分成3组逐一排名,并列,排名连续
/*
orderid qty rownumber rank denserank NTILE
30 10 1 1 1 1
10 10 2 1 1 1
80 10 3 1 1 1
40 10 4 1 1 1
30 15 5 5 2 2
30 20 6 6 3 2
22 20 7 6 3 2
21 20 8 6 3 2
10 30 9 9 4 3
30 30 10 9 4 3
40 40 11 11 5 3*/
2.已有范围和缺失范围
这是一个很实际的问题,偷懒下,我直接用书上的数据了.
USE tempdb;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1
GO
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.T1(col1) VALUES(1);
INSERT INTO dbo.T1(col1) VALUES(2);
INSERT INTO dbo.T1(col1) VALUES(3);
INSERT INTO dbo.T1(col1) VALUES(100);
INSERT INTO dbo.T1(col1) VALUES(101);
INSERT INTO dbo.T1(col1) VALUES(103);
INSERT INTO dbo.T1(col1) VALUES(104);
INSERT INTO dbo.T1(col1) VALUES(105);
INSERT INTO dbo.T1(col1) VALUES(106);
--首先要看的是缺失范围的查询:
--方法1:找出断点的第一个数字后,找出距离这个断点数字最近的数字-1(关于找出缺失数字我前面的学习笔记有介绍了几种方法)
select col1+1 as startnum,
(select MIN(col1)-1 from T1 where k.col1<col1) as endnum
from T1 k
where not exists(select * from T1 where k.col1+1=col1)
and col1<(select MAX(col1) from T1)--防止最大数字的后一个数字出现
--方法2:利用前后2个数字相减的差来判断是否连续
select CUr+1 as stratnum,nxt-1 as endnum
from(
select col1 as cur,(select MIN(col1) from T1 where k.col1<col1) as nxt
from T1 k) p
where nxt-cur>1
/*
stratnum endnum
----------- -----------
4 99
102 102
*/
--再来看看已有范围的查询
--方法1:利用比当前值大的最接近最小间断值的值
select MIN(col1) as startnum,
grp as endnum
from(
select col1,
(select MIN(col1) from T1 a where a.col1>=k.col1 and
not exists(select * from T1 where a.col1+1=col1 )) as grp
from T1 k ) z
group by grp
--方法2:利用ROW_NUMBER 以前看P梁写过,就是产生一个分组因子(上面那个方法是通过子查询来确定一个分组因子其实)
select MIN(col1) as startnum,
max(col1) as endnum
from(
select col1,
col1-ROW_NUMBER()over(order by col1) as grp
from T1 k ) z
group by grp
/*
startnum endnum
----------- -----------
1 3
100 101
103 106
*/
相关推荐
sqlserver中常用函数----字符串函数---- ASCII() --函数返回字符表达式最左端字符的ASCII 码值 CHAR() --函数用于将ASCII 码转换为字符 --如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值 ...
主要内容包括SQL的基础理论、查询优化、查询算法及复杂度,以及在使用子查询、表表达式、排名函数、数据聚合和透视转换、TOP和APPLY、数据修改、分区表、特殊数据结构等实际应用时会遇到的各种高级查询问题和解决...
学习sql server 和sql 的两本经典的著作: 《sql server 2005 技术内幕 T-SQL查询》 《sql server 2005 技术内幕 T-SQL程序设计》 网上大多的资源都是英文的,好容易找到中文的了,上传上来和大家分享。 这两本书都...
卸载清除工具,干净的卸载sql server 标题: Microsoft SQL Server 2008 R2 安装程序 ------------------------------ 出现以下错误: MOF 编译器无法连接 WMI 服务器。原因可能是语义错误(例如,与现有 WMI ...
笔记是本人学习SQLServer一段时间后重新整理出来的,适合有一些入门基础的人学习。 ├─01 安装及使用 │ SQLServer2005安装及使用.txt │ ├─02 常用函数 │ function.sql │ ├─03 建表、建库 │ create.sql ...
Sql Server2005对t-sql的增强之排名函数
sqlserver个人学习笔记
java 连接sqlserver使用的java-sqlserver-connect.jar包。 java连接sqlserver使用的jar包,包含jre7、jre8两种使用 sqlserver.ja
SQLServer2005技术内幕T-SQL查询的代码示例
SQL server 2008
SQL Server2000设计与T-SQL编程电子书籍,对学习ms sql 与存储过程代码编写是一本很不错大书
T-SQL性能调优秘笈-基于SQL Server 2012窗口函数,适合数据库进阶。
本人的SQL server2017笔记。 笔记内容:基础增删改查、视图操作、索引操作、索引种类、多表、ml查询、Transact-SQL、存储过程、触发器、SQL server自动化服务、SQL server集成服务…
Sqlserver2008--学习笔记(自己总结)
sql server 数据库学习笔记,希望对初学数据库的人能有所帮助
里面包含了sqlserver 2005和2008官网下载地址--sqlserver2008安装详解--以及错误及解决办法
MS SQL SERVER学习笔记,包括数据库技术简介,SQL server简介,完整性,数据库查询等。
记录学习笔记,对初学者很有好处。具体步骤详见文档,
SQL 的基础理论 、查询优化、查询 事法及复杂度,以及在使用子 查询、表表达式、排名函数、数据聚合和透视转换、TOP 和 APPLY、数据修改、分区表、特殊数据结构等实际 应用时会遇到的各种高级查询问题和解决方案.
Microsoft SQL Server 2008技术内幕:T-SQL查询