SQL2005 Tips 排序函数
来源：互联网 发布：手机号码改号软件 编辑：程序博客网 时间：2024/05/21 18:48
USE tempdb -- or your own test database
CREATE TABLE SpeakerStats
(
speaker VARCHAR(10) NOT NULL PRIMARY KEY,
track VARCHAR(10) NOT NULL,
score INT NOT NULL,
pctfilledevals INT NOT NULL,
numsessions INT NOT NULL
)
--名字、议题、平均得分、填写评价的与会者相对于参加会议的与会者数量的百分比以及该演讲者发表演讲的次数
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan', 'Sys', 3, 22, 4)
INSERT INTO SpeakerStats VALUES('Ron', 'Dev', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Kathy', 'Sys', 8, 27, 2)
INSERT INTO SpeakerStats VALUES('Suzanne', 'DB', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Joe', 'Dev', 6, 20, 2)
INSERT INTO SpeakerStats VALUES('Robert', 'Dev', 6, 28, 2)
INSERT INTO SpeakerStats VALUES('Mike', 'DB', 8, 20, 3)
INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
INSERT INTO SpeakerStats VALUES('Brian', 'Sys', 7, 22, 3)
INSERT INTO SpeakerStats VALUES('Kevin', 'DB', 7, 25, 4)
SELECT * FROM SpeakerStats
--1
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
speaker, track, score
FROM SpeakerStats
ORDER BY score DESC
/* for SQL 2000
SELECT
(
SELECT COUNT(*)
FROM SpeakerStats AS S2
WHERE S2.score > S1.score
OR (S2.score = S1.score
AND S2.pctfilledevals > S1.pctfilledevals)
OR (S2.score = S1.score
AND S2.pctfilledevals = S1.pctfilledevals
AND S2.numsessions > S1.numsessions)
OR (S2.score = S1.score
AND S2.pctfilledevals = S1.pctfilledevals
AND S2.numsessions = S1.numsessions
AND S2.speaker < S1.speaker)
) + 1 AS rownum,
speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats AS S1
ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker
*/
--2 Paging
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker
--当用户发出多个请求时，该方法就不能满足需要了，因为该查询的每个调用都需要您对表进行完整扫描，以便计算行号。
--当用户可能反复请求不同的页时，为了更有效地进行分页，
--请首先用所有基础表行（包括计算得到的行号）填充一个临时表，并且对包含这些行号的列进行索引
SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, *
INTO #SpeakerStatsRN
FROM SpeakerStats
CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #SpeakerStatsRN(rownum)
SELECT rownum, speaker, track, score
FROM #SpeakerStatsRN
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker
--segment
--可以在行组内部独立地计算排序值，而不是为作为一个组的所有表行计算排序值。
--为此，请使用PARTITION BY 子句，并且指定一个表达式列表，以标识应该为其独立计算排序值的行组。
--例如，以下查询按照“score DESC, speaker”顺序单独分配每个track 内部的行号(具有相同track 的每个行组单独计算行号)
SELECT track,
ROW_NUMBER() OVER(
PARTITION BY track
ORDER BY score DESC, speaker) AS pos,
speaker, score
FROM SpeakerStats
ORDER BY track, score DESC, speaker
--与ROW_NUMBER 不同的是，RANK 和DENSE_RANK
--向在排序列中具有相同值的行分配相同的排序
--当ORDER BY 列表不唯一，并且您不希望为在ORDER BY 列表中具有相同值的行分配不同的排序时，
--RANK 和DENSE_RANK 很有用
--当ORDER BY 列表不唯一时，ROW_NUMBER 是不确定的，而RANK 和DENSE_RANK 总是确定的
--当ORDER BY 列表唯一时，ROW_NUMBER、RANK 和DENSE_RANK 产生完全相同的值
SELECT speaker, track, score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
RANK() OVER(ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER(ORDER BY score DESC) AS drnk
FROM SpeakerStats
ORDER BY score DESC
--NTILE 使您可以按照指定的顺序，将查询的结果行分散到指定数量的组(tile) 中。
--每个行组都获得不同的号码：第一组为1，第二组为2，等等。
--您可以在函数名称后面的括号中指定所请求的组号，在OVER 选项的ORDER BY 子句中指定所请求的排序。
--组中的行数被计算为total_num_rows / num_groups。如果有余数n，则前面n 个组获得一个附加行。
--因此，可能不会所有组都获得相等数量的行，但是组大小最大只可能相差一行。
SELECT speaker, track, score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
NTILE(3) OVER(ORDER BY score DESC) AS tile
FROM SpeakerStats
ORDER BY score DESC
SELECT speaker, track, score,
CASE NTILE(3) OVER(ORDER BY score DESC)
WHEN 1 THEN 'High'
WHEN 2 THEN 'Medium'
WHEN 3 THEN 'Low'
END AS scorecategory
FROM SpeakerStats
ORDER BY track, speaker
/*
derived table query
*/
USE AdventureWorks
GO
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OE(EmployeeID, NumOrders, MaxDate)
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN
(SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OM(EmployeeID, NumOrders, MaxDate)
ON E.ManagerID = OM.EmployeeID
;WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
), ---pay attention to the comma
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE
--在CTE 内部，您并非只能引用恰好在它前面定义的CTE；
--相反，您可以引用之前定义的所有CTE。
--请注意，不允许向前引用：CTE 可以引用在它前面定义的CTE 和它本身（参阅后文中的递归查询），
--但是不能引用在它后面定义的CTE。例如，如果您在同一WITH 语句中定义了CTE C1、C2、C3，
--则C2 可以引用C1 和C2，但是不能引用C3
WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
),
NumsCTE(Num)
AS
(
SELECT 1 AS Num
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
),
StepsCTE(Step, Fromval, Toval)
AS
(
SELECT
Num,
CAST(MN + ROUND((Num-1)*((Diff+1)/4.), 0) AS INT),
CAST(MN + ROUND((Num)*((Diff+1)/4.), 0) - 1 AS INT)
FROM MinMaxCTE CROSS JOIN NumsCTE
),
HistogramCTE(Step, Fromval, Toval, Samples)
AS
(
SELECT S.Step, S.Fromval, S.Toval, COUNT(EmployeeID)
FROM StepsCTE AS S
LEFT OUTER JOIN EmpOrdersCTE AS OE
ON OE.Cnt BETWEEN S.Fromval AND S.Toval
GROUP BY S.Step, S.Fromval, S.Toval
)
SELECT * FROM HistogramCTE
/*
CTE query example
*/
USE tempdb -- or your own test database
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
--递归查询
--返回某个雇员（例如，empid=7 的Robert）及其所有级别的下属
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 7
UNION ALL
-- Recursive Member (RM)
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
WITH EmpCTEJanet(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0 AS lvl
FROM Employees
WHERE empid = 3
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTEJanet as M
ON E.mgrid = M.empid
WHERE lvl < 2
)
SELECT empid, empname
FROM EmpCTEJanet
WHERE lvl = 2
DECLARE @empid AS INT, @lvl AS INT
SET @empid = 3 -- Janet
SET @lvl = 2 -- two levels
;WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
WHERE lvl < @lvl
)
SELECT empid, empname
FROM EmpCTE
WHERE lvl = @lvl
--使用提示在已经调用一定数量的递归迭代之后强行终止查询。
--可以通过在外部查询的结尾添加OPTION(MAXRECURSION value) 做到这一点
;WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
OPTION (MAXRECURSION 2)
SELECT * FROM Employees e
UPDATE Employees
SET
mgrid = 14 --null
WHERE empid=1
;WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
--check dead reference
--锚定成员和递归成员中的对应列必须具有相同的数据类型、长度和精度。
--这就是生成path 值的表达式在两个成员中都被转换为varbinary(900) 的原因
WITH EmpCTE(empid, path, cycle)
AS
(
SELECT empid,
CAST('.' + CAST(empid AS VARCHAR(10)) + '.' AS VARCHAR(900)),
0
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid,
CAST(M.path + CAST(E.empid AS VARCHAR(10)) + '.' AS VARCHAR(900)),
CASE
WHEN M.path LIKE '%.' + CAST(E.empid AS VARCHAR(10)) + '.%' THEN 1
ELSE 0
END
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
WHERE M.cycle = 0
)
SELECT * FROM EmpCTE
WHERE cycle = 1
--fix data
UPDATE Employees SET mgrid = NULL WHERE empid = 1
--迄今为止提供的递归示例具有一个经理锚定成员和一个用于检索下属的递归成员。
--某些请求则要求执行相反的操作；例如，请求返回James 的管理路径（James 及其所有级别的经理）。以下代码提供了对该请求的应答：
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 14
UNION ALL
SELECT M.empid, M.empname, M.mgrid, E.lvl+1
FROM Employees as M
JOIN EmpCTE as E
ON M.empid = E.mgrid
)
SELECT * FROM EmpCTE
--您还可以使用递归查询来计算聚合，例如，直接或间接向每个经理汇报的下属的数量：
WITH MgrCTE(empid,mgrid, lvl)
AS
(
-- 每个雇员的经理ID 的行
SELECT empid, mgrid, 0
FROM Employees
WHERE mgrid IS NOT NULL
UNION ALL
-- 递归成员返回先前返回的经理的经理的经理
SELECT M.empid, M.mgrid, lvl + 1
FROM Employees AS M
JOIN MgrCTE AS E
ON E.mgrid = M.empid
WHERE M.mgrid IS NOT NULL
)
SELECT mgrid
--,empid
, COUNT(*) AS cnt
FROM MgrCTE
GROUP BY mgrid
--作为针对单父节点层次结构的请求的另一个示例，假设您希望返回Nancy 的按照层次依赖项排序和缩进的下属。
--以下代码恰好完成该任务，它按照同辈的雇员ID 对它们进行排序：
WITH EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
(
SELECT empid, empname, mgrid, 0,
CAST(empid AS VARBINARY(900))
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE(' | ', lvl)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol --empid 值对同辈进行排序
WITH EmpPos(empid, empname, mgrid, pos)
AS
(
SELECT empid, empname, mgrid,
ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY empname) AS pos
FROM Employees
),
EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
(
SELECT empid, empname, mgrid, 0,
CAST(pos AS VARBINARY(900))
FROM EmpPos
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
CAST(sortcol + CAST(E.pos AS BINARY(4)) AS VARBINARY(900))
FROM EmpPos AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE(' | ', lvl)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
--要按照其他任何属性或属性组合对同辈进行排序，
--只须在ROW_NUMBER 函数的OVER 选项的ORDER BY 列表中指定所需的属性，而不是指定empname
- SQL2005 Tips 排序函数
- sql2005排序函数
- php的tips（数组排序函数）
- 函数 tips
- SQL2005 Tips XML 处理例子(1)
- sql2005新增排序查询
- sql2005新增排序方法
- SQL2005使用函数
- sql2005 自定义函数
- sql2005排名函数
- SQL2005中的窗口函数
- sql2005时间函数
- SQL2005 自定义函数
- SQL2005 自定义函数
- sql2005时间函数
- sql2005的函数
- SQL2005 新增函数
- 归并排序 tips && codes
- SQL 2005 Tips --事务处理The current transaction cannot be committed and cannot support operations that write to the log file
- NET 4.0中的新功能介绍：契约式设计 (Design By Contracts)
- ThreadLocal用法
- UML常见的图
- Struts2 文件上传
- SQL2005 Tips 排序函数
- sh,ksh,csh三者之间的差异
- 注册表及其命令用法
- 将基于MFC的对话框应用程序修改为服务程序
- hibernate中知识总结
- Symbian C++ 各种类型之间的转换
- SQL 2005 tips --Pivot
- jQuery validate 分组验证
- 在Java中利用动态代理实现数据库连接与事务的自动管理
