SQL排名开窗函数

注意事项：由于设计器内置的是 Sqlite 数据库，在使用上很多语法都不支持，请将 Sqlite 数据库文件迁移到主流数据库(MySql、MSSQL、Oracle…)后运行。

迁移方法：使用第三方软件迁移内置 FRDemo 数据库到指定数据库

本文运行环境：Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

                     Copyright (c) Microsoft Corporation

                     Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

1. 问题描述

对于一些特殊的数据表，我们想对它的某一数据列进行排序，表中又不存在排序字段，但又不想在模板中进行排序处理，此时我们就需要在建立数据集时直接通过 SQL 进行处理了。

2. 实现思路

在创建数据集时用 SQL 的开窗排名函数处理，然后进行直接调用。

3. 函数介绍

下面主要解析四种常用的排序开窗函数:

3.1、ROW_NUMBER() OVER ()  

ROW_NUMBER 直接分组，前面的序号唯一且连续

3.2、RANK() OVER ()

RANK()并列排序，值相同序号并列，后面的值跳跃

3.3、DENSE_RANK() OVER ()

DENSE_RANK 并列排序，值相同序号并列，后面的值连续

3.4、NTILE(n) OVER ()

不常用，NTILE(4)将所有的行分为 4 组，然后 10/4=2 余 2，表示每组 2 行，前面的 2 行+1（3 行）

4. 示例4.1准备数据

-- ----------------------------

-- Table structure for test_table

-- ----------------------------

DROP TABLE [dbo].[test_table]

GO

CREATE TABLE [dbo].[test_table] (

[id] int NOT NULL IDENTITY(1,1) ,

[name] nvarchar(50) NULL ,

[price] nvarchar(50) NULL

)

GO

-- ----------------------------

-- Records of test_table

-- ----------------------------

SET IDENTITY_INSERT [dbo].[test_table] ON

GO

INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'141', N'香蕉', N'20')

GO

GO

INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'142', N'苹果', N'25')

GO

GO

INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'143', N'梨', N'17')

GO

GO

INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'144', N'菠萝', N'24')

GO

GO

INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'145', N'西瓜', N'24')

GO

GO

INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'146', N'油桃', N'16')

GO

GO

INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'147', N'哈密瓜', N'9')

GO

GO

INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'148', N'水蜜桃', N'33')

GO

GO

INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'149', N'柚子', N'10')

GO

GO

INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'150', N'橘子', N'8')

GO

GO

SET IDENTITY_INSERT [dbo].[test_table] OFF

GO

4.2 排名 SQL
SELECT name, price, ROW_NUMBER () OVER (ORDER BY price DESC) AS '排名1', --ROW_NUMBER直接分组，前面的序号唯一且连续 RANK () OVER (ORDER BY price DESC) AS '排名2', --RANK()并列排序，值相同序号并列，后面的值跳跃 DENSE_RANK () OVER (ORDER BY price DESC) AS '排名3', --DENSE_RANK并列排序，值相同序号并列，后面的值连续 NTILE (4) OVER (ORDER BY price DESC) AS '排名4' --不常用，NTILE(4)将所有的行分为4组，然后10/4=2余2，表示每组2行，前面的2行+1（3行） FROM test_table;

4.3 预览效果

执行上述 SQL 语句，可得到如下效果：

技术方案文档迁移测试

点个赞

向老司机致敬！

还有一个比较实用的是分组排名 over(partition by xx)