ＳＱＬ技术内幕－6 rank()over(order by XX COLLATE) 的用法

DECLARE @Names TABLE (
  name VARCHAR(20)
);

INSERT INTO @Names VALUES
  ('DeSzmetch'),('DESZMETCH'),('DESZMETCK'),('DesZmetch'),('deszmetch');

SELECT
  name,
  RANK() OVER (ORDER BY name COLLATE Latin1_General_BIN) AS [Lat...BIN],
  RANK() OVER (ORDER BY name COLLATE Traditional_Spanish_CI_AS) AS [Tra...CI_AS],
  RANK() OVER (ORDER BY name COLLATE Latin1_General_CS_AS) AS [Lat...CS_AS],
  RANK() OVER (ORDER BY name COLLATE Latin1_General_CI_AS) AS [Lat...CI_AS],
  RANK() OVER (ORDER BY name COLLATE Hungarian_CI_AS) AS [Hun..._CI_AS]
FROM @Names
ORDER BY name COLLATE Latin1_General_BIN;
GO

　　---order 不要后面的字段也可以不同，但是都会按照最后一个排列顺序

DECLARE @Student table   --学生成绩表
(
 id int,  --主键
 Grade int, --班级
 Score INT, --分数
 name VARCHAR(120)  --名字
)
INSERT INTO @Student VALUES
(1,1,88,'A1'),(2,1,66,'B1'),(3,1,75,'C1'),(4,2,30,'D1'),(5,2,70,'E1'),
( 6,2,80,'F1'),(7,2,60,'G1'),(8,3,90,'H1'),(9,3,70,'I1'),(10,3,80,'j1')

--SELECT * FROM  @Student

SELECT name,Score,
rank() OVER(ORDER BY Grade DESC) AS rankGrade,
rank() OVER(ORDER BY Score DESC) AS rankScore,
rank() OVER(ORDER BY id DESC) AS rankid
FROM  @Student

　　

转载于:https://www.cnblogs.com/alphafly/p/4307245.html